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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
"store my files" -- are you talking about the final xlsx file with the powerpivot model and reports? However you can get that up on sharepoint is peachy. Doesn't matter.

If you are talking about the source files (that PQ is pulling from), ... then it kinda matters, but only if you are worried about auto-refresh scenarios.
 
Upvote 0
Sorry, that was kind of vague, wasn't it?

I meant the source files - where should they be housed?
 
Last edited:
Upvote 0
Thank you very much -

First, TimRodman - I have set each individual connection to refresh whenever the file is opened (online or on the desktop) - and it seems to work without an additional app.

MattAllington - I do have my Sharepoint lists mapped on my PC, I just didn't know if that mapping would work (efficiently?) with Excel Online.

Miguel.escobar - do I have to utilize the SharePoint.Contents if it's mapped to my PC.


So, it sounds to me like just having it mapped to my PC works best (and easiest in PQ, from what I saw). However, when a coworker shares a SharePoint site with me and I have edit permission, will that show up where Sharepoint is mapped?

Like I said in my original post, I just want to use both products most efficiently. Also, I'd be totally cool with having both source files and reports on Sharepoint - does that matter in this conversation?

Cliff
 
Last edited:
Upvote 0
you don't need anything in your PC if you use SharePoint.Contents. The good thing about this is that you can share this query with other colleagues and that'll work for everyone whereas the local file path would be something that might only work for you
 
Upvote 0
Miguel - thank you. Would I be able to read the entire "site" like a folder in PQ? I plan on adding a lot of files to the site each week and would rather not have to set up a separate query for each file added.

Is that how SharePoint.Contents works?

Do I access SharePoint.Contents through PQ's "From Other Sources>From SharePoint Lists"? I thought the list was different than the documents. When I use the "From SharePoint Lists" I do not see my workbooks, just

ComposedLooks
Microfeed
UserInformationList
 
Last edited:
Upvote 0
This is how it looks like:
30afm90.png


It is pretty similar to the "From Folder" experience
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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