Split values in one row into multiple

[han_rj]

I have a column country and customers which are colon separated , I would like each entry be created into new row like the after screenshort in power query

Before

ID	Country	Customer
1	US;Britan;India	Cain;Joel

After

ID	Country	Customer
1	US	Cain
1	US	Joel
1	Britan	Cain
1	Britan	Joel
1	India	Cain
1	India	Joel

[lbendlin]

Another option:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoNtnYqyixJzLP2zEvJTASKOCdm5ll75afmKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Country " = _t, Customer = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Country ", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Country "),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"Customer", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Customer")
in
    #"Split Column by Delimiter1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

[jennratten · Accepted solution]

Hello @han_rj  - thank you for posting in the Fabric Community.

Here is how you can solve the scenario you posted.  I have also attached a PBIX with the solution for your convenience. Please let me know if you have any questions about this.

RESULT:

SCRIPT:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoNtnYqyixJzLP2zEvJTASKOCdm5ll75afmKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Country = _t, Customer = _t]),

 // Build the cross-join as a nested table per row, then expand it
    #"Add a CrossJoin" =
        Table.AddColumn(
            Source,
            "CrossJoin",
            (r as record) as table =>
                let
                    // Split to lists
                    CountriesRaw = if r[Country] = null then {} else Text.Split(r[Country], ";"),
                    CustomersRaw = if r[Customer] = null then {} else Text.Split(r[Customer], ";"),

                    // Clean lists: trim + remove blanks
                    CountriesClean = List.Select(List.Transform(CountriesRaw, each Text.Trim(_)), each _ <> ""),
                    CustomersClean = List.Select(List.Transform(CustomersRaw, each Text.Trim(_)), each _ <> ""),

                    // OPTIONAL: remove duplicates within each list - if you objective is to omit duplicates; they are omitted in your sample result
                    Countries = List.Distinct(CountriesClean),
                    Customers = List.Distinct(CustomersClean),

                    // Create one record per combination (aka cartesean)
                    Combos =
                        List.Combine(
                            List.Transform(
                                Countries,
                                (cty) => List.Transform(Customers, (cust) => [Country = cty, Customer = cust])
                            )
                        ),

                    // Turn into a table
                    ComboTable = Table.FromRecords(Combos, type table [Country = text, Customer = text])
                in
                    ComboTable,
            type table
        ),
    #"Removed Other Columns" = Table.SelectColumns(#"Add a CrossJoin",{"ID", "CrossJoin"}),

    // Expand the nested table into rows
    #"Expanded CrossJoin" =
        Table.ExpandTableColumn(
            #"Removed Other Columns",
            "CrossJoin",
            {"Country", "Customer"},
            {"Country", "Customer"}
        ),

    // Keep final columns (adjust if you have more columns you want to keep)
    #"Final" = Table.SelectColumns(#"Expanded CrossJoin", {"ID", "Country", "Customer"}),
    #"Changed Type" = Table.TransformColumnTypes(Final,{{"ID", Int64.Type}, {"Country", type text}, {"Customer", type text}})
in
    #"Changed Type"

If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.

Proud to be a Microsoft Fabric Super User