Forum Discussion
Generate parent id from BOM level information
Anonymous
Your DAX formula also seems correct.
JUST REPLACE FIRSTNONBLANK with LASTNONBLANK
Zubair_Muhammad Thank you, that DAX suggestion works, but I could not get the Power Query one to work. It only generates null values.
Now I have two working solutions to this:
Parent =
MAXX(
TOPN(
1;
FILTER(
'BOM';
'BOM'[Level] = EARLIER('BOM'[Level]) - 1 &&
'BOM'[Index] < EARLIER('BOM'[Index])
);
'BOM'[Index]; DESC
);
'BOM'[Index]
)and
Parent =
CALCULATE(
LASTNONBLANK(VALUES(BOM[Index]);0);
FILTER(
ALL(BOM);
BOM[Level] = EARLIER(BOM[Level]) - 1 &&
BOM[Index] < EARLIER(BOM[Index])
)
)
Community Championhi Anonymous
I am attaching the pbix file with Power Query Custom formulas
It works with me
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
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
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.