Split columns by Delimiter and create multiple rows in M

[kegoosse]

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

[kegoosse]

Thanks, both options work for me.

[Mariusz]

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.

[Gordonlilj · Accepted solution]

Hi,

You can also split into rows