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
edhans's avatar
edhans
Community Champion
5 years ago

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's avatar
sglendenning
Helper II
5 years ago

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'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 :