How to get SharePoint Library metadata / custom columns in Excel via PowerQuery?

[Olivier Travers]

When I use the "Export in Excel" option from a SharePoint library, this creates a query file with the iqy extension. This can be saved as an odc connection for future refreshes, but either way I'd rather handle everything via Power Query rather than mix PQ with "old school" data connections.

Export to Excel from SharePoint library

The Excel table built via the iqy file or resulting odc connection (through Data > Existing Connections) includes extra columns that we added to the library

User-created columns

via iqy query / odc connection

However using PQ to retrieve a SharePoint folder only returns default columns:

Get Data > From SharePoint Folder

I also had a look at results returned by the SharePoint Lists and Odata feed connectors, and they return way more than what I need (and I'm not sure they have what I do need, I'd rather mess around with these if I don't have to).

Is there a way to get the same results via Power Query that you get from "Export in Excel" as detailed above? Any help appreciated.

This is with Excel 2016 (up-to-date O365 ProPlus monthly release) and SharePoint Online. I haven't tried from PBI Desktop though I'd expect the same behavior.

[#2]

Hi @Olivier Travers ,

I had the same use case.  My workaround was:

1. Query 1 - load the document library data as a SharePoint List into a table. 
2. Query 2 use SharePoint.Files to load in the file data. 
3. In Query 2 merge in Query 1 using an inner join matched on the filename
4. Expand out the metadata columns I needed from the library data table
5. Remove all columns other than the required metadata and Content
6. Fileted hidden files
7. Extract table from content
8. Expand table
9. Remove content column
10. Other data processing as required

This gave me exactly what I needed.  Hopefully it will help you too.