Forum Discussion
Parent Child Hierarchy In Large Data Set
My most critical output would be to get the Company from the original Parent to be filled in for all the children. In my data the Parent becomes the child on the next purchase by the same customer and continues in this fashion until we come to present day. There is no relationship between the Parent ID's within the field...it is essentially a number ID of the next sale of a product so I can't sort by either Parent nor Child and use a Fill Down function. A recursive operation sounds like it would work to search the Child ID back through all the Parent ID's until a Parent ID with a Company name is found, but I have no idea how to pull that off. I will try to pull together a data set that is representative of what I have and what I am after.
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.
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 :