Convert 2 columns of data in excel power query to one header row with multiple columns

Question

I am trying to figure out how to change 2 columns of data into one header row with multiple columns in Excel Power Query. It's my understanding that Query keeps the Excel file size small, and is less on the processes, as opposed to using tons of vlookups or pivot tables. I'm open to VBA if that's a better option.

For example, I have Column A with a list of names. Then, column B has another list of names with multiple instances of the same name(s). The names in column A are individuals assigned to report to individuals in B.

I'm trying to create a query (or VBA if better) where the names in B become the row headers, and the names in A fall under the corresponding person in each header.

I hope that makes sense. Thank you in advance for your help!

Here's a screenshot, demonstrating what I'm working with, and the end result I'm trying to get:

I would probably go with VBA and storing data in a dictionary of dictionaries. Or loop column B assigning column A values to the appropriate next free row in the matching column (matched by column header or fixed column reference). — QHarr

Olly Olly · Accepted Answer · 2018-08-21T10:17:15

You can use Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ListEmployees = Table.Group(Source, {"Supervisor Name"}, {{"Employees", each Text.Combine([Employee Name],","), type text}}),
    CountEmployees = Table.AddColumn(ListEmployees, "Count", each List.Count(Text.Split([Employees],","))),
    SplitEmployees = Table.SplitColumn(ListEmployees, "Employees", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),List.Max(CountEmployees[Count])),
    Transpose = Table.Transpose(SplitEmployees),
    PromoteHeaders = Table.PromoteHeaders(Transpose, [PromoteAllScalars=true])
in
    PromoteHeaders

Make sure your source data is structured as a Table (listobject).

Convert 2 columns of data in excel power query to one header row with multiple columns

1 Answers