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

%3CLINGO-SUB%20id%3D%22lingo-sub-266002%22%20slang%3D%22en-US%22%3EHow%20to%20get%20SharePoint%20Library%20metadata%20%2F%20custom%20columns%20in%20Excel%20via%20PowerQuery%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-266002%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20I%20use%20the%20%22Export%20in%20Excel%22%20option%20from%20a%20SharePoint%20library%2C%20this%20creates%20a%20query%20file%20with%20the%20iqy%20extension.%20This%20can%20be%20saved%20as%20an%20odc%20connection%20for%20future%20refreshes%2C%20but%20either%20way%20I'd%20rather%20handle%20everything%20via%20Power%20Query%20rather%20than%20mix%20PQ%20with%20%22old%20school%22%20data%20connections.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F55323iF210870A556AD1F1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22SP-export-excel.png%22%20title%3D%22SP-export-excel.png%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EExport%20to%20Excel%20from%20SharePoint%20library%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20Excel%20table%20built%20via%20the%20iqy%20file%20or%20resulting%20odc%20connection%20(through%26nbsp%3B%3CEM%3EData%20%26gt%3B%20Existing%20Connections%3C%2FEM%3E)%20includes%20extra%20columns%20that%20we%20added%20to%20the%20library%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F55325i7252FF09EB5A17E3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22user-columns.png%22%20title%3D%22user-columns.png%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EUser-created%20columns%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F55326i911FD0A2BCCA2B21%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22excel-cols.png%22%20title%3D%22excel-cols.png%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3Evia%20iqy%20query%20%2F%20odc%20connection%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHowever%20using%20PQ%20to%20retrieve%20a%20SharePoint%20folder%20only%20returns%20default%20columns%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F55328iF6967C7372C73976%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22sp-pq.png%22%20title%3D%22sp-pq.png%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EGet%20Data%20%26gt%3B%20From%20SharePoint%20Folder%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20also%20had%20a%20look%20at%20results%20returned%20by%20the%20SharePoint%20Lists%20and%20Odata%20feed%20connectors%2C%20and%20they%20return%20way%20more%20than%20what%20I%20need%20(and%20I'm%20not%20sure%20they%20have%20what%20I%20do%20need%2C%20I'd%20rather%20mess%20around%20with%20these%20if%20I%20don't%20have%20to).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20get%20the%20same%20results%20via%20Power%20Query%20that%20you%20get%20from%20%22Export%20in%20Excel%22%20as%20detailed%20above%3F%20Any%20help%20appreciated.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThis%20is%20with%20Excel%202016%20(up-to-date%20O365%20ProPlus%20monthly%20release)%20and%20SharePoint%20Online.%20I%20haven't%20tried%20from%20PBI%20Desktop%20though%20I'd%20expect%20the%20same%20behavior.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-266002%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Contributor

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 libraryExport 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 columnsUser-created columns

via iqy query / odc connectionvia iqy query / odc connection

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

Get Data > From SharePoint FolderGet 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.

0 Replies