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

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,""})),"|")