Forum Discussion
Helper ISplit columns by Delimiter and create multiple rows in M
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
Hi,
You can also split into rows
3 Replies
- Mariusz
Community Champion
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.
GordonliljSolution Sage
Hi,
You can also split into rows
kegoosseThanks, both options work for me.
