Power Query Challenge

[Datatouille]

Hello everyone,

I'd be interested in your thoughts for this Power Query question !

Here is how my database looks like

Img ID	Age	Gender
1	30;40;50	M;F;M
2	35;38;45	F;F;M
3	23;28;50	M;M;F

and here is how it should look like after cleansing:

Img ID	Age	Gender
1	30	M
1	40	F
1	50	M
2	35	F
2	38	F
2	45	M
3	23	M
3	28	M
3	50	F

"Double Unpivot" is impossible here. This is what I am doing for the moment to solve the issue:

I am creating two separate tables referencing my original table.

I am using the first separate table to delete Age Column, split& unpivot Gender

I am using the second separate table to delete Gender Column, split & unpivot Age

Then, I am merging these 2 tables based on Imgid and the index generated.

I am wondering if you guys have a better approach (probably using M tricks?) ?

[MarcelBeug · Accepted solution]

You can split the text in Age and Gender to nested lists,

then add a column with the nested lists combined in nested tables,

expand the nested tables,

remove the old columns and

rename and type the new columns.

    SplittedAge = Table.TransformColumns(PreviousStep,{{"Age", each Text.Split(_,";")}}),
    SplittedGender = Table.TransformColumns(SplittedAge,{{"Gender", each Text.Split(_,";")}}),
    #"Added Custom" = Table.AddColumn(SplittedGender, "AgeAndGender", each Table.FromColumns({[Age],[Gender]})),
    #"Expanded AgeAndGender" = Table.ExpandTableColumn(#"Added Custom", "AgeAndGender", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded AgeAndGender",{"Age", "Gender"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Age"}, {"Column2", "Gender"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Gender", type text}, {"Age", type text}})