Power Query and SharePoint

cmcreynolds

Active Member
Joined
May 21, 2015
Messages
295
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
 
wow - so, if someone looks at a large report more than once per day, does that mean they get charged more? Or is it the refresh rate?
 
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Take a look at this (click here). It looks to me like it's the total data storage capacity, not how much data flows through.

I think of it like the amount of storage space that you have on your cell phone, not the amount of data that you use each month with the cell phone company.
 
Upvote 0
It would also allow me to save my queries separately instead of me copying the same file and re-naming it - seems really important as our company grows.
 
Upvote 0
@TimRodman

In Excel's Connections, if I select the data to refresh, say every 60 minutes, will the refreshed data show up regardless who opens it? In other words, in SharePoint, would someone who has "can view" permission be able to see the refreshed data?
 
Upvote 0
That will only apply if you actually have the Excel file open. It's not related to SharePoint as far as I know. It should apply to "view" permissions too, but I haven't actually tried it.
 
Upvote 0
@TimRodman

Well, after having PowerBI a couple of months and having MANY runarounds, come to find out you cannot simply automatically refresh an Excel workbook. It refreshes the connection between the workbook and PowerBI, but not the connections between the workbook and datasources (like Salesforce).

And since our Sharepoint (something about farm?) doesn't support "PowerPivot for Sharepoint", I will try to convince our IT to get Power Update.

Lots of man hours lost to trying to get PowerBI to do something it wasn't meant to do, unfortunately. *SIGH*
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top