How to merge and expand a table multiple times until a condition is met

[Anonymous]

Hello everyone,

This is a rare situation that I want to solve using Power Query. I have the solution in an Excel VBA macro but it takes so much time to process that it is not pratical.

I have a table with two columns with the list of components and raw materials required to produce said components and finished products. Example:

Finished Product A	Component A1
Finished Product A	Component A2
Finished Product B	Component B1
Finished Product B	Component B2
Component A1	Component B1
Component A1	Component B2
Component A2	Raw Material 1
Component B1	Raw Material 2
Component B2	Raw Material 3
Component B2	Raw Material 4

I want a new table, have the first column with only finished products and the, sequentialy merge the column above, adding the second column multiple times until I finished with only raw materials:

Finished Product A	Component A1	Component B1	Raw Material 2
Finished Product A	Component A1	Component B2	Raw Material 3
Finished Product A	Component A1	Component B2	Raw Material 4
Finished Product A	Component A2	Raw Material 1
Finished Product B	Component B1	Raw Material 2
Finished Product B	Component B2	Raw Material 3
Finished Product B	Component B2	Raw Material 4

This is a very simple example, there are products that have so many components and raw materials that can generate 30 columns or more.

I want to merge the blueprint table (first table) with the new blueprint matrix table until the last column doesn't find any more matches (there are no more components to match, only raw materials)

So basically:

While number of matchs <> 0 Do merge tables and expand component and raw materials column

[mahoneypat]

Here is an example of how to transform your example data to your desired result.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvMyyzOSE1RCCjKTylNLlFwVNJRcs7PLcjPS80D8gyVYnUIqzLCrsoJRZUTDrPQVEHMQnECFlNwy2PoNwLKByWWK/gmlqQWZSbmKKCb4GSIrgLdDCcMM4wJqjBRio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    Split = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    ForMerging = Table.Buffer(Split),
    #"Filtered Rows" = Table.SelectRows(Split, each Text.Contains([Column1], "Finished")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Column2"}, ForMerging, {"Column1"}, "Filtered Rows", JoinKind.LeftOuter),
    #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"Column2"}, {"Column2.1"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Filtered Rows", {"Column2.1"}, ForMerging, {"Column1"}, "Expanded Filtered Rows", JoinKind.LeftOuter),
    #"Expanded Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Filtered Rows", {"Column2"}, {"Column2.2"})
in
    #"Expanded Expanded Filtered Rows"

At first, I thought you could solve this with the PATH function in DAX (or with its M equivalent).  However, since each child can have multiple parents, it wouldn't work.

The approach I provided is the brute force approach.  You can continue the same pattern for the number of levels you have (30?).  It isn't as elegant as one that would automatically iterate until it hits the end, but hopefully you just have to do this one time and then it will be automatic upon refresh.  Note that I included Table.Buffer on the table to be merged many times, which should speed things up significantly.

If you do want to adapt the M equivalent of the PATH function to do this, here it is - https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-s-PATH-function-equivalent-Custom-Column...

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Smauro · Accepted solution]

I'll have a go at it, but you should know it's still not an easy calculation if you have a lot of products.

@AnonymousI've written a function, which takes 4 inputs:

1) The table name -- which should be your last query step,

2) The name of the 1st column,

3) The name of the 2nd column, and

4) The start of the finished products' name; e.g. "Finished Product" if all finished products are of the kind "finished product XXXX"

    fnFindProductLine =
        (Tbl as table, FirstColumn as text, CheckColumn as text, FinishedProduct as text) as table =>
            let
                TbFinal =Table.SelectRows(Tbl,each Text.Contains(Record.Field(_, FirstColumn),FinishedProduct,Comparer.OrdinalIgnoreCase)),
                TbCheck =Table.SelectRows(Tbl,each not Text.Contains(Record.Field(_, FirstColumn),FinishedProduct,Comparer.OrdinalIgnoreCase)),
                MergeToTheEnd =
                    (tb as table, c1 as text, c2 as text, k as number) =>
                        let
                            NewColumnName = "Product Line " & Text.From(k),
                            NewTable =
                                Table.ExpandTableColumn(
                                    Table.NestedJoin(
                                        tb,
                                        {c2},
                                        TbCheck,
                                        {c1},
                                        "mrg",
                                        JoinKind.LeftOuter
                                    ),
                                    "mrg",
                                    {CheckColumn},
                                    {NewColumnName}
                                ),
                            IsRaw =
                                if List.IsEmpty(List.RemoveNulls(Table.Column(NewTable, NewColumnName))) then
                                    Table.RemoveColumns(
                                        NewTable,
                                        NewColumnName
                                    )
                                else
                                    @MergeToTheEnd(
                                        NewTable,
                                        c1,
                                        NewColumnName,
                                        k + 1
                                    )
                        in
                            IsRaw,
                CallTheRecursiveFn =MergeToTheEnd(TbFinal,FirstColumn,CheckColumn,1)
            in
                CallTheRecursiveFn,

I've used this table as reference:

   PreviousStep = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZDBCoQgFEV/RVy3yaYPGC13A9E2WsgkjDCjQxn9fkGrdzVayjnee3nDwLXzbvnYiXVzmNZ3ZE9ecBV+/+CtP14lH4t7S+QtSSx5kQXWmUUmZFKu+d3/Crk4eG829jLRzs58GTbIEg3skEkGtqTGA42K7FTJCuC4QSUrazQESWiwoaGXapG3lGvkOrdg3AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [c1 = _t, c2 = _t]),

So, in my case 1) is PreviousStep , 2) is "c1", 3) is "c2", 4) is "Finished Product"

And I called it:

    FindProductLine = fnFindProductLine( PreviousStep, "c1", "c2", "Finished Product" )

If you somehow have a "Product line {n}" column on your start table, the code will not work, because it names the new columns this way, as you see.

Full dummy code:

Spoiler

let
   PreviousStep = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZDBCoQgFEV/RVy3yaYPGC13A9E2WsgkjDCjQxn9fkGrdzVayjnee3nDwLXzbvnYiXVzmNZ3ZE9ecBV+/+CtP14lH4t7S+QtSSx5kQXWmUUmZFKu+d3/Crk4eG829jLRzs58GTbIEg3skEkGtqTGA42K7FTJCuC4QSUrazQESWiwoaGXapG3lGvkOrdg3AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [c1 = _t, c2 = _t]),


    fnFindProductLine =
        (Tbl as table, FirstColumn as text, CheckColumn as text, FinishedProduct as text) as table =>
            let
                TbFinal =Table.SelectRows(Tbl,each Text.Contains(Record.Field(_, FirstColumn),FinishedProduct,Comparer.OrdinalIgnoreCase)),
                TbCheck =Table.SelectRows(Tbl,each not Text.Contains(Record.Field(_, FirstColumn),FinishedProduct,Comparer.OrdinalIgnoreCase)),
                MergeToTheEnd =
                    (tb as table, c1 as text, c2 as text, k as number) =>
                        let
                            NewColumnName = "Product Line " & Text.From(k),
                            NewTable =
                                Table.ExpandTableColumn(
                                    Table.NestedJoin(
                                        tb,
                                        {c2},
                                        TbCheck,
                                        {c1},
                                        "mrg",
                                        JoinKind.LeftOuter
                                    ),
                                    "mrg",
                                    {CheckColumn},
                                    {NewColumnName}
                                ),
                            IsRaw =
                                if List.IsEmpty(List.RemoveNulls(Table.Column(NewTable, NewColumnName))) then
                                    Table.RemoveColumns(
                                        NewTable,
                                        NewColumnName
                                    )
                                else
                                    @MergeToTheEnd(
                                        NewTable,
                                        c1,
                                        NewColumnName,
                                        k + 1
                                    )
                        in
                            IsRaw,
                CallTheRecursiveFn =MergeToTheEnd(TbFinal,FirstColumn,CheckColumn,1)
            in
                CallTheRecursiveFn,

    FindProductLine = fnFindProductLine( PreviousStep, "c1", "c2", "Finished Product" )
in
    FindProductLine

Hope this works for your case.

Best,

Spyros

[Anonymous]

What I was really missing was a method for looping the merge function, like you did with "@". By adapting my code with it I managed to solve the problem! Thank you very much for the help! That solution is brilliant, although this is not very fast, it's still better than using VBA and I can use it in both Power BI and Excel Power Query.

[Anonymous]

give  a try to this:

#####edited####

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "hY+xDsIgEEB/hTB3ATXqKFA3ksa1YSCWRBItptb4+yKHw9E2HV/u8bhrW3r2vX/dXEeaIXTv60hOtKIyPJ6hd30kRk21bvF5SyBLLLQKC1pohZnK8nzynsf5xX6ItqMbvL2TsiBYaZQNMWlsVo0tXNvI33qSIeKIckumQxRDlDeJTszr/yx9pvNMJbPGlJsKzB1QDbRHdMgEPxypMV8=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type text) meta [Serialized.Text = true])
    in
      type table[col1 = _t, col2 = _t]
  ),
  ct = Table.TransformColumnTypes(Source, {{"col1", type text}, {"col2", type text}}),
  add = (tab, n) => 
    let
      ac = Table.AddColumn(
        tab, 
        "col" & Text.From(n), 
        each List.Distinct(
          Table.SelectRows(tab, (r) => r[col1] = Record.Field(_, "col" & Text.From(n - 1)))[col2]
        )
      ),
      tabn = Table.ExpandListColumn(ac, "col" & Text.From(n))
    in
      tabn,
  steps = List.Generate(
    () => [y = ct, n = 3, x = true], 
    each [x], 
    each [y = add([y], [n]), n = [n] + 1, x = not List.IsEmpty(
      List.Intersect({Table.Column(y, "col" & Text.From([n] - 1)), y[col1]})
    )], 
    each [y]
  )
in
  Table.SelectRows(List.Last(steps), each not List.Contains(Source[col2], [col1]))

[Anonymous]

just to make the code more readable, using the  formatter that makes the view more pleasant 😀

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "hY+xDsIgEEB/hTB3ATXqKFA3ksa1YSCWRBItptb4+yKHw9E2HV/u8bhrW3r2vX/dXEeaIXTv60hOtKIyPJ6hd30kRk21bvF5SyBLLLQKC1pohZnK8nzynsf5xX6ItqMbvL2TsiBYaZQNMWlsVo0tXNvI33qSIeKIckumQxRDlDeJTszr/yx9pvNMJbPGlJsKzB1QDbRHdMgEPxypMV8=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type text) meta [Serialized.Text = true])
    in
      type table[col1 = _t, col2 = _t]
  ),
    add = (tab, n) => 
    let
      ac = Table.AddColumn(
        tab, 
        "col" & Text.From(n), 
        each List.Distinct(
          Table.SelectRows(tab, (r) => r[col1] = Record.Field(_, "col" & Text.From(n - 1)))[col2]
        )
      ),
      tabn = Table.ExpandListColumn(ac, "col" & Text.From(n))
    in
      tabn,
  last = (tab, coln) => not List.IsEmpty(
    List.Intersect({Table.Column(tab, "col" & Text.From(coln - 1)), tab[col1]})
  ),
  steps = List.Generate(
    () => [y = Source, n = 3, x = true], 
    each [x], 
    each [y = add([y], [n]), n = [n] + 1, x = last([y], [n])], 
    each [y]
  )
in
  Table.SelectRows(List.Last(steps), each not List.Contains(Source[col2], [col1]))