Power Query create PIVOT but each Pivot column should have their "sub-column"-CodePudding

Title explained: I have a txt file, I read it into excel, create a Query to edit it. My txt file is

Is this even achievable somehow? It looks so messed up for me. I am almost sure it is possible to create the targeted table in VBS, but I am trying to get better with Power Query.

Thank you in advance!

CodePudding user response：

Try this code in Powerquery. You unpivot, combine the two columns, then pivot back. Gender would show up as a column when there is data in there.

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Shop", "Material nr"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Shop", "Attribute"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Header"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Header]), "Header", "Value")
in  #"Pivoted Column"

Note if you feel like it, also read in the text file directly without needing to load the data into an excel table first

let Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Temp\a.txt"), null, null, 1252)}),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("___", QuoteStyle.Csv) ),
#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Shop", "Material nr"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Shop", "Attribute"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Header"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Header]), "Header", "Value")
in  #"Pivoted Column"