Forum Discussion
Sorting multi header table in power query
Create a function that ingests a single file
(f)=> let Source = Csv.Document(f,[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]), LZ = List.Skip(List.Transform(List.Zip({Record.ToList(Source{0}),Record.ToList(Source{1})}),each Text.Combine(_,"|")),2), #"Removed Bottom Rows" = Table.RemoveLastN(Source,1), #"Removed Top Rows" = Table.Skip(#"Removed Bottom Rows",2), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), #"Replaced Headers" = Table.RenameColumns(#"Promoted Headers",List.Zip({List.Skip(Table.ColumnNames(#"Promoted Headers"),2),LZ})), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Headers", {"Status", "Created Date"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"|"}, QuoteStyle.Csv, false), {"Pick Storage", "Attribute"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Value] <> null) and ([Pick Storage] <> "Totals")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value"), #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Created Date", type date}},"de") in #"Changed Type1"Then apply that to the list of files
And finally expand the table.
Thank you for all the proposed solutions.
I wish I could understand them in detail to learn better but it might take too much time to explain.
The function I proposed explains the process step by step. There is no magic to this - you need to transform the source format step by step into something usable.
The only "fancy" part is the harvesting of the dangling headers
LZ = List.Skip(List.Transform(List.Zip({Record.ToList(Source{0}),Record.ToList(Source{1})}),each Text.Combine(_,"|")),2)
You can use PowerQueryFormatter to make the code look easier to understand.
LZ
= List.Skip(
List.Transform(
List.Zip({Record.ToList(Source{0}), Record.ToList(Source{1})}),
each Text.Combine(_, "|")
),
2
)We are treating the first two rows as if they were lists. We combine them via List.Zip, and then convert the result to pipe delimited strings. Since the first two columns are ok we skip them.