Hi ,
I have two columns in a table, one is the main category, and the other's a sub category.
so I am wondering if it is possible to pivot this data using Power Query, with an end result of the Main Category being the "Column Header" and the values will be the "Sub Category".
I found an almost similar thread about this but it doesn't seem to work on my case, probably because we have a different format.
Thanks in advance and any help will be much appreciated.
P.S., I attached the file I have here as an example for reference
Hi Lloyd,
The structure is different indeed, I doubt that you can get the result you want using PQ menu commands, you have to use the Advanced Editor to write a custom step.
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category2", type text}, {"Sub-Category", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Category2"}, {{"Grouped", each _, type table [Category2=nullable text, #"Sub-Category"=nullable text]}}),
KeepSubCategOnly = Table.TransformColumns(#"Grouped Rows",{{"Grouped", each _[#"Sub-Category"], type text}}),
Pivot = Table.FromColumns(KeepSubCategOnly[Grouped],KeepSubCategOnly[Category2])
in
Pivot
The last step Pivot is using Table.FromColumns which is not in the menu, so you have to write it in Editor.
