Skip to content

Instantly share code, notes, and snippets.

@wildmichael
Last active May 28, 2021 09:46
Show Gist options
  • Save wildmichael/3b994e3d6411fd09ad171bb28bcd80bf to your computer and use it in GitHub Desktop.
Save wildmichael/3b994e3d6411fd09ad171bb28bcd80bf to your computer and use it in GitHub Desktop.
Power Query function to convert a Excel XML Spreadsheet to a table
(fileContents) => let
Source = Xml.Tables(fileContents),
Table = Source{2}[Table]{0}[Table],
#"Expanded Table" = Table.ExpandTableColumn(Table, "Table", {"Cell"}, {"Table.Cell"}),
#"Added Index" = Table.AddIndexColumn(#"Expanded Table", "Index", 0, 1),
#"Expanded Table.Cell" = Table.ExpandTableColumn(#"Added Index", "Table.Cell", {"Data"}, {"Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Expanded Table.Cell", "Data", {"Element:Text"}, {"Element:Text"}),
#"Grouped Rows" = Table.Group(#"Expanded Data", {"Index"}, {{"Count", each Table.AddIndexColumn(_, "Cum",1,1), type table}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Cum", "Element:Text"}, {"Cum", "Element:Text"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Count", {{"Cum", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Count", {{"Cum", type text}}, "en-US")[Cum]), "Cum", "Element:Text"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true])
in
#"Promoted Headers"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment