Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?) ?
Solved! Go to 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}})
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}})
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |