Power Query and SharePoint

[cmcreynolds]

Hello, I'm asking a more hypothetical question regarding Power Query and where to store files. My (very small) company is looking to do away with their network drives and move storage to MS SharePoint. I have access to SharePoint and they've created a path in my "file explorer." We also have OneDrive available, but we do not have the PowerBI product.

So, I, the analyst, have been asked to build reports from several sources and having fallen in love with PQ and PowerPivot, I would like to utilize those before any other Add-In.

My question is where to store my files. I completely get how PQ and PP work with network connections, but I'm confused as to how to create connections with SharePoint. Since there is a path to my sites on my file explorer, can I just use those (i.e. "From File") options or is it better to use "From Web"?

I'm still wrapping my head around what SharePoint is. What I do know is OneDrive will not allow you to create paths on your machine from another person's shared folder (my main issue). I just want to utilize SharePoint and PowerQuery correctly and most effectively.

Thank you.

Cliff

 

[TimRodman]

Everyone needing to view the report. That's why Power Update is typically the cheaper option since it takes care of the refresh and users only need Excel to view the reports.

You can even publish the reports to PDF or PNG so the user doesn't even need Excel. The downside is that the reports aren't interactive.

 

[cmcreynolds]

Noted.

Guess what (didn't?) happened yesterday - colleagues opened the reports in Excel Online and didn't get an error. The only change I made was unchecking the "Refresh data when opening the file" option in "Connections". I understand that the report they are viewing isn't up-to-minute, but at least they can see the last time I saved the file. Now...back to the original idea of refreshing without good ol' Cliff doing it...

Power Update, Power BI, and PowerPivot for SharePoint. I will look into those.

Thank you all for your help.

 

[TimRodman]

Yes! Excel Online is the way to go. It works on mobile too. You just need an automated way to refresh the data. That's where Power Update is your friend. It's a very lightweight program, it's free for one workbook, and it's super simple to setup and use (no IT help needed). I'd encourage you to give it a try (click here).

 

[cmcreynolds]

And it would be payable for "my" use - in other words, not per "users" who view the file. It's basically a way for me to schedule refreshes and it doesn't care who views the actual files. (right?)

 

[TimRodman]

That's right. When they open the workbook (in Excel or Excel Online), the data has already been refreshed so it's just regular Excel at that point.

You could accomplish the same thing without Power Update by manually refreshing the data in your reports every morning. If it doesn't take you very long, that might work just fine.

 

[cmcreynolds]

Well, right now - we're looking at maybe 5 reports. BUT, we are taking on a lot of new clients which will require more reporting. So, $500 maybe worth it - over Power BI, that is.

 

[cmcreynolds]

I now have a trial subscription to Power BI...so we'll see (Their decision, not mine)

 

[TimRodman]

Good move. I'm sure that you won't regret it. July 24th is the key date to watch. For more info, click here.

 

[cmcreynolds]

I can't interpret what "general availability" means - free with Office 365 or some other subscription? Or just that now everyone can buy a subscription. lol gotta love marketing.

 

[TimRodman]

It means that it will no longer be in preview (or beta). It will continue to be free for 1GB of data/user. You only pay if you go beyond 1GB.