Split values in one row into multiple

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