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.
Hi Power BI community,
I have a table consisting of an ID and a nationality, lets call it "table"
ID Nationality
AA Belgian
AB Dutch
AC Belgian, Dutch
Some individuals have double nationalities. Both are mentioned in the column nationality and seperated by a comma.
I would like to transform the table so individuals with double nationalities get 2 rows: 1 for each nationality.
ID Nationality
AA Belgian
AB Dutch
AC Belgian
AC Dutch
Does someonehave an idea which m-functions to use for such a transformation?
Thanks in advance
Solved! Go to Solution.
Thanks, both options work for me.
Hi @kegoosse
Its few steps in Query Editor.
1. In Query Editor, select your column Nationaly then in Transform Ribon find Split Column > By Delimiter > Select Custom from the dropdown and type ", " in your text box.
2. Select Both new columns and click Unpivot Columns.
Please see M code below with all the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQ8tEBJR8kpNSc9MzFPKVYHKOQEFnIpLUnOgAg4I6vRUYDKxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ID " = _t, Nationality = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Nationality", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Nationality.1", "Nationality.2"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"ID "}, "Attribute", "Nationality"), #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Columns",{"ID ", "Nationality"}) in #"Removed Other Columns"
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You can also split into rows
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |