Forum Discussion

sglendenning's avatar
sglendenning
Helper II
5 years ago

Parent Child Hierarchy In Large Data Set

I have a data set where only the original parent has a data field entry (in the example below it is Company) that I want to get to when looking at the child.  The Company is only filled out on the fi...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

pending further information to develop and make the code efficient according to the data structure, I propose this idea:

a function:

 

let
    
    genealogy=(adamo) =>
        let 
        Co=ct[Company]{adamo},
         g=   List.Generate(
        ()=>[Par=ct[Parent]{r},Ch=ct[Child]{r},Last=true,r=adamo],
        each [Last],
        each [r=List.PositionOf(ct[Child],ct[Parent]{[r]}), Par=ct[Parent]{r},Ch=ct[Child]{r},Last=Logical.From(1+r)],

        each ct{[r]}&[Company=Co]
    )
in Table.FromRecords(g)
in
    genealogy

 

 

that receive a seed in input  and give a table out output:

 

 

if you invoke with seed 0

 

if you invoke with seed 4

 

 

There is, of course, a lot to improve in search expressions, but it can be done specifically once you know the structure of the data and also the expected result.

 

 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

or somethink like this:

 

 

let

   genealogy=(adamo) =>
        let 
        Co=ct[Company]{adamo},
         g=   List.Generate(
        ()=>[Par=ct[Parent]{r},Ch=ct[Child]{r},Last=true,r=adamo],
        each [Last],
        each [r=List.PositionOf(ct[Child],ct[Parent]{[r]}), Par=ct[Parent]{r},Ch=ct[Child]{r},Last=Logical.From(1+r)],

        each [Par]
    )
in  ct{adamo}&[Parent=Text.Combine(g,", "), Child=Text.Combine({"null"}&List.RemoveLastN(g,1),", ")],

    Source = Table.AddIndexColumn(ct,"idx",0,1),
    seeds = Table.SelectRows(Source, each ([Child] = ""))[idx],
    recs=List.Transform(seeds,each genealogy(_))

in
    Table.FromRecords(recs)

 

 

which, when invoked against this table:

 

 

gives  this table :