Home

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
Olivier Travers
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.

 

SP-export-excel.pngExport 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-columns.pngUser-created columns

excel-cols.pngvia iqy query / odc connection

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

sp-pq.pngGet 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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies