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?

Click here to check the table 1 and 2

Alexis Olson · Accepted Answer · 2018-12-01 17:52:45Z

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.

answered Dec 1, 2018 at 17:52

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aleksei Zhigulin · Accepted Answer · 2018-12-14 10:54:26Z

0

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

edited Dec 14, 2018 at 10:54

answered Dec 14, 2018 at 10:47

Aleksei Zhigulin

1,6241 gold badge10 silver badges11 bronze badges

Comments

tomi09pl · Accepted Answer · 2018-12-01 12:16:31Z

-1

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.

answered Dec 1, 2018 at 12:16

tomi09pl

582 silver badges5 bronze badges

Collectives™ on Stack Overflow

Power Query - Concatenate and Transpose

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related