Forum Discussion

Olivier Travers's avatar
Olivier Travers
Copper Contributor
Oct 02, 2018

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

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 ra...
sharepoint
Jamsie's avatar
Jamsie
Copper Contributor
Apr 15, 2021

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.

Resources