Power Query - Concatenate and Transpose

Question

I"ve a data table (1) below and want the result as in table (2). How can I do this?

Answer 1

I'd suggest the following:

• Delete duplicate rows using Remove Rows > Remove Duplicates
• Group your table using concatenation (see this question)
• Split the concatenated State column using Split Column > By Delimiter.

Answer 2

You could use this code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    group = Table.Group(Source, {"Name"}, {{"State", each Text.Combine(List.Distinct([State]), "|")}}),
    split = Table.SplitColumn(group, "State", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"State.1", "State.2", "State.3"})
in
    split

result

Answer 3

Have a look a this topic: VBA excel, concatenate cells when there are duplicates .

You could use that vlookupall function and then use "Text to column" on your result.