Creating a loop in Power BI Query

[Chris_de]

Hey erveryone,

I have a table in Power Bi which includes a parent child connection. Its a list of materials which contains other materials. The Parent column has all Materials that have other Materials inside which can be found in the Material_child column. But now most of Materials in the child column are also made of materials. So the are also in the parent column. So my first approach was to duplicate the whole table multiple times  and merge material_child from original with material_parent from copy 1, then again child from copy 1 with parent from copy 2 and so on until I no longer have materials in the child column. 

That is extremely computationally intensive and puts a strain on the servers of the data source. Refreshing takes ages. 

Is it possible to create some kind of loop which creates copys and connect them as I want automatically until there is no more result in the last material_child column? 

Here is an picture of my table. It has probably 200.000 rows.

[Chris_de]

Hey @AlienSx ,

thank your for the reply. I created an excel example to visualize what I'm trying to do: 

It basically a Partslist. Every Material that is made of other Materials is a parent. The Material it is made of is a child. But every child might be also made of Materials so it can be but not has to be also in the parent column. I want to merge the child column with the parent column as long as there is no more childs and I have reached the last level in my parts list. Here is the example as text:

Material_parent	Material_child
555666	888999
555666	777888
888999	999666
777888	111222
777888	222333
222333	444555

[AlienSx]

@Chris_de I am not sure if it's gonna work much faster than your current setup but give this a try. 

let
// original data table
    s = #table(
        {"Material_parent", "Material_child", "info"},
        {{555666, 888999, "a"}, {555666, 777888, "b"}, {888999, 999666, "c"},
        {777888, 111222, "d"}, {777888, 222333, "e"}, {222333, 444555, "f"}}
    ),
// txform id columns to text 
    types = Table.TransformColumns(s, {{"Material_parent", Text.From}, {"Material_child", Text.From}}),
// make a record with all childs
    chi = Record.Combine(
        Table.ToList(
            types, 
            (x) => Record.FromList({x}, {x{1}})
        )
    ),
// this recursive function goes over the chain from bottom to top
    chain = (par as text, lst as list) as list =>
        let r = Record.FieldOrDefault(chi, par) in if r is null then lst meta [n = List.Count(lst)] else @chain(r{0}, r & lst),
// this is a list of terminal childs, these are rows in final table 
    rows = List.Difference(types[Material_child], types[Material_parent]),
// transform rows into a list of all par-child data using chain function
    tra = List.Transform(
        rows, 
        (x) => chain(x, {})
    ),
// here I have to calculate max number of columns for Table.FromList 
    cols = List.Max(List.Transform(tra, (m) => Value.Metadata(m)[n])),
// get final table from our list of rows
    to_tbl = Table.FromList(tra, (x) => x, cols, null)
in
    to_tbl

core idea behind this is to get a record with ["child_id" = parent_id] fields so that we can arrange a loop (recursive function "chain" in this case) with a help of Record.FieldOrDefault. Rows of our final table are those ending with "terminal childs" which in turn can be calculated using List.Difference([child_id], [parent_id]). I did not bother with column names. If this won't ruin your server then you can easily rename columns later. This approach is not the best in terms of efficiency becasue we go over and over same chains so that there is a room for improvements. I hope you've got the idea. You're asking for a loop in M afterall 🙂

[AlienSx]

Hello, @Chris_de loops in M can be easily arranged by either List.Generate or custom recursive function. It's not quite clear what are you trying to get in the end. Please post several rows of original data (in the form of text table like many others do so we can grab them for processing) and show the result you expect to get after transformations.