Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Generate parent id from BOM level information

I am trying to figure out a DAX formula (or Power Query) to determine each rows parent if it exists.   I have data which contains the index, level and component information. Parent column is the on...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    6 years ago

    Anonymous 

     

    Your DAX formula also seems correct.

    JUST REPLACE FIRSTNONBLANK with LASTNONBLANK

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks, figured it out. You cannot just copy and paste those Power Query formulas because they have some reference to each step done with the data before.

 

Any ideas on how to generate the PATH directly in Power Query? Or how to generate the Level 1, Level 2, Level ..., Level n automatically? Since I do not know how many levels the data might include.

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

     

    In Power Query, we can use List.Generate to replicate the PATH function.

     

    If you can copy paste some data with expected results, I will try to write it for you

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      The expected result using DAX with the example you sent is just a new column with:

      Column = PATH(BOM[Index]; BOM[Custom])

       

      If this can be done with Power Query, then I can split the column to all the levels required without creating the columns manually and worrying if the level depth changes.

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous 

         

        You can use this custom column

         

        I am attaching sample file with this formula

         

        =let mylist=
        {[Index]} & (let myparent=[Custom] in
        List.Generate(()=>[x=0,y=myparent,w=1],each [w] > 0, each [z=
        [y], x=Table.SelectRows(#"Changed Type1",each  [Index]=z)[Custom],
        y=x{0},
        w=List.Count(x)],each [y]))
        in
        Text.Combine(List.Reverse(List.RemoveItems(List.Transform(mylist,each Number.ToText(_)),{null,""})),"|")