Forum Discussion
Solved
Sorting multi header table in power query
Morning, I have folder that reiceves multiple files in it and I will be reading all the files from that folder and combining them. Good thing is that tables in the csv files are all some layout. Th...
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.
lbendlin I am not sure if that is going to help in my case since my data looks like this:
When I import it in to power bi thats why I need to sort it in power query before I use it in report.
In my data 'Pick Storage' which is in column two, row one.
Rest in row one G2P1, G2P5 and G5P0 are values of 'Pick Storage' column that ideally should be across one column and not multiple rows.
Something like this:
I hope it makes it more clear.
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.