Transpose grouped values Power Query

[Aldonin]

Hi everyone: Important: I tried to solve this using both plain excel and Power Query since these are the only tools I can use, it would be great if the solutions/ideas consider those tools only, thank you so much!

Here it goes:

I have data structured in the following way: Left column shows a list of "Events", right column includes the dependent Participants for each event all comma-separated.

Host Event	Dependent Participants
Event1	P1,P2,P3
Event2	P7,P9

I'm trying and failing miserably to transform this table into something like the table below (I believe it's called tabular).

Host Event	Dependent Participants
Event1	P1
Event1	P2
Event1	P3
Event2	P7
Event2	P9

Any ideas/suggestions on how I can solve this using excel or Power Query?

Thank you so much in advance!

 

[Alex Blakenburg]

This is straight forward enough in Power Query.

Start with converting your data into a table:

• Select any cell in your data range
• Either Insert > Table (3rd button from the left )

OR Ctrl + T​

• Click on the tab Table Design and in the white box on the far left put in the table name tbl_Events

Now still with a cell in the Table Selected:

• Data > From Table Range
• in PQ > Home > Advanced Editor (3rd Button from the left
• Replace the code you find there with the code below
• Close & Load OR Close & Load To

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Events"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Dependent Participants", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Dependent Participants.1", "Dependent Participants.2", "Dependent Participants.3"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Host Event"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Host Event", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Value", "Dependent Participants"}})
in
    #"Renamed Columns"

 

[RoryA]

FWIW, manual steps to do this:
1. Load the data into PQ.
2. Select the Dependent Participants column, and on the Transform tab choose Split Column - By delimiter
3. Choose comma for the delimiter, click the Advanced Options dropdown at the bottom and choose to split into Rows.

 

[Alex Blakenburg]

@Aldonin, please use Rory's method.

Rory's posting made me revisit mine. I am very conscious of not hardcoding columns in the Query steps but still did not pick up that my Split Columns did hard code some columns.
Rory's method did not.

So apart from recommending you convert your data into a table and explicitly naming it (rather than have PQ call it Table1), please follow Rory's suggestion.

 

[Aldonin]

Thanks both, this place is just awesome. Really appreciate all the help, solution worked perfect!

 

[RoryA]

Glad we could help.