Last active
May 28, 2021 09:46
-
-
Save wildmichael/3b994e3d6411fd09ad171bb28bcd80bf to your computer and use it in GitHub Desktop.
Power Query function to convert a Excel XML Spreadsheet to a table
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| (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