Parent Child Hierarchy In Large Data Set

[sglendenning]

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 first entry and will be blank for the child entries.  I would like to get to a couple different views; 

1. What is the original Parent number and Company.
2. What is the original Parent and all Childer leading to most recent child.
3. Bring in the original parent company to all children

I have 300K rows of data and a parent could have dozens of children.

Parent	Child	Name	Type	Company
111111		John	New	Apple
222222	111111	John	Red
8675309	222222	John	Yellow
99999999	8675309	John	Red

[ziying35]

Hi, @sglendenning 

Such a problem can be solved by writing recursive code, but I haven't used it on that large amount of data. Can you simulate more amount of data and the effect you want to achieve and upload this file to the cloud drive and share the link here? Because @edhans  and I are equally unable to figure out what you expect to achieve

[edhans]

I'm not sure exactly what you want. Can you give us expected output for this? I know Company needs to be filled out, and that is easy. Just right-click on the Company field and select Fill Down.

becomes 

Those cells have to be null, not blank. If they are blank without the word null in them, then first go to Transform, Replace Values, and leave the first field blank, put null in the second. Then do the fill down.

To help with the rest, I'd need a screen shot or a clearer understanding of the end goal.

[sglendenning]

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.

[Anonymous]

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]

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 :