How to convert two columns of data into multiple columns where one column is to be a header and the-CodePudding

So I has two columns like this:

and I want this to appear like this:

I've done this in excel using the new dynamic array formulas but I want to automate this in power query.

I want this without using advanced editor please.

Please help!

I tried the unpivot function in power query (without aggregation) but I get errors, I also tried to use the group funtion which also did not work. I also tried making a list of the "Account Group" but then I didn't know what to do after that. I am out of wits now.

CodePudding user response：

You can not do it without advanced editor.

Assuming you felt like using it,

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Accout Group"}, {{"Count", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type), type table}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Account Name", "Index"}, {"Account Name", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Count", List.Distinct(#"Expanded Count"[#"Accout Group"]), "Accout Group", "Account Name"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in #"Removed Columns"