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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
"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.
 

cmcreynolds

Active Member
Joined
May 21, 2015
Messages
295
Sorry, that was kind of vague, wasn't it?

I meant the source files - where should they be housed?
 
Last edited:

miguel.escobar

Active Member
Joined
Dec 7, 2012
Messages
294

ADVERTISEMENT

if you're using SharePoint 2013 or SharePoint online then you can use this:
https://support.office.com/en-sg/ar...026-b3b7-bccc9cf563e9?ui=en-US&rs=en-SG&ad=SG

and simply query the files from your SharePoint folder (and reading the binaries) instead of downloading the files locally.

Note that the speed and performance may vary depending on your SharePoint server.
 

cmcreynolds

Active Member
Joined
May 21, 2015
Messages
295

ADVERTISEMENT

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:

miguel.escobar

Active Member
Joined
Dec 7, 2012
Messages
294
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
 

cmcreynolds

Active Member
Joined
May 21, 2015
Messages
295
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:

miguel.escobar

Active Member
Joined
Dec 7, 2012
Messages
294
This is how it looks like:
30afm90.png


It is pretty similar to the "From Folder" experience
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

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
Top