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]

@cmcreynolds - If refreshing on open works for you, I would stick with it. If you find that refreshing is taking a while and your users are complaining, then you might want to look at Power Update. That's the situation I eventually found myself in (click here). Power Update can point to a regular folder or a SharePoint folder and do the refresh for you. Then users just open the Excel file knowing that the data is as current as the last refresh.

Regarding your question about Power Query -> From Other Sources -> From SharePoint List (pictured below), that will allow you to connect to SharePoint List data and it maps to the SharePoint.Tables Power Query Function. Miguel's picture is showing how to connect to SharePoint Library data and is using the SharePoint.Contents Power Query Function. I don't think there is a nice menu option for SharePoint.Contents. Miguel, please correct me if I'm wrong.

 

[miguel.escobar]

SharePoint.Contents is one of those hidden gems Power Query has

 

[TimRodman]

@Miguel - I guess I haven't had to use SharePoint.Contents yet because I try to source my data from a Database or a SharePoint list since they provide more structure. But I can see how many people only have Excel as a data storage option so they need SharePoint.Contents. It would be nice to have it on the menu though with a prompt for Site Address, Library Name, Excel File Name, and Excel Table Name. Maybe not that last one though since people don't always use Tables.

@cmcreynolds - To use SharePoint.Contents, just do the following:

1. Go to Power Query -> Get External Data -> From Other Sources -> Blank Query (don't be afraid, this is the real "power" part of Power Query)
2. Paste the following in the formula bar (which I stole from Miguel's screenshot). Replace MySharePointSite with the URL to your SharePoint site and MyLibrary with the name of your SharePoint library that has the Excel files in it (might be called "Documents"):
   =SharePoint.Contents("MySharePointSite"){[Name="MyLibrary"]}[Content]
3. You will now see a list of Excel files. Click the word "Binary" next to the Excel file that you want to access
4. You will now see a list of Worksheets, Tables, and Named Ranges. Click the word "Table" next to the one that you want to access
5. You should now see your data

 

[ralliartur]

There are more options to do it.

1. Open sharepoint site, go to Library -> open with explorer
2. Windows Explorer window will open -> copy full address from navigation bar (it should be like https://sp_server/sp_site_address)
3. Remove "https:" from string, and change / to \ -> in the end the path will look like \\sp_server\sp_site_address (you can use notepad for this operation)
4. Now you can paste new link to "From folder" option in Power Query

 

[TimRodman]

Nice!

 

[cmcreynolds]

Okay - so would utilizing SharePoint.Contents be "better" (I know that's quite vague, too) than putting the path as ralliartur suggested?

TimRodman - Power Update looks pretty awesome! I'm not sure that my company (I'm the only person creating reports - we're that small) would need something that powerful - I'm just pulling maybe 5000 records at a time from SalesForce or our own CRM. BUT, I will definitely look into it if we become bigger and require such pulls. Oh - and thanks for your instructions!

OH - and I know this is a SharePoint question - but do I still have to worry about putting spaces in the names of "sites"? Is that still a "thing"?

 

[TimRodman]

Maybe you could try the spaces and let us know if they work.

 

[cmcreynolds]

Seems to work fine with spaces (but the URL copies over with % instead of spaces, which is per usual, I think).

NEW SNAG - it's asking me for credentials, then telling me I'm not authorized. I built the sites and I'm the only one accessing them! In the URL where it's asking me permission it contains a specific document - surely I don't have to give myself permission for each document, do I?

 

[cmcreynolds]

So, I removed the "https://" and it let me access the documents.

NOW - after creating a new column "Excel.Workbook([Content])", it gives me an error for the first document and I can't figure out why. Is it because of headers or something?

 

[cmcreynolds]

That first document is constructed just like the others, but still gives me an error instead of table (the error is "DataFormat.Error:....not a valid Excel document" - but it is! *.xlsx; no macros; no connections; no data model; no pivot tables; just a table with 13 rows of data. Very frustrating.