Forum Discussion
Conditional Fill Down
I'm working with a WBS structure that does not have parent entity/child entity columns. I still need to break down the WBS structure by Levels (00.00=Level 2, 00.00.00=Level 3, 00.00.00.00=Level 4) WITH it's title. I've gotten as far as the below table.
Now, I need to fill down the values in each Level - 1,2,3,4,5 BUT where this breaks down is the record that null is the aqua color in Level 3. The value above 000.01.01 - Analytical Services by Company - shouldn't be in that record with the aqua color that should remain empty because you can see in the record to the left, it's an entirely different WBS structure. You'll see that as you move along deeper into the WBS structure, this becomes a re-occurring problem.
I've looked up multiple videos on conditional fill down and nothing seems to fit this scenario, or at least I can't get it to work.
Any help would be appreciated.
| WBS Title | WBS - Copy | Level 1 | Level 2 | Level 3 | Level 4 | Level 5 |
| 000 - Example Services | 000 | 000 - Example Services | null | null | null | null |
| 000.01 - Analytical Services | 000.01 | null | 000.01 - Analytical Services | null | null | null |
| 000.01.01 -Analytical Services by Company | 000.01.01 | null | null | 000.01.01 - Analytical Services by Company | null | null |
| 000.01.01.01 - Base Operations | 000.01.01.01 | null | null | null | 000.01.01.01 - Base Operations | null |
| 000.01.01.01.01 - Compliant Operations | 000.01.01.01.01 | null | null | null | null | 000.01.01.01.01 - Compliant Operations |
| 000.01.01.01.02 - Reliability | 000.01.01.01.02 | null | null | null | null | 000.01.01.01.02 - Reliability |
| 000.01.01.01.03 - Sample Analysis | 000.01.01.01.03 | null | null | null | null | 000.01.01.01.03 - Sample Analysis |
| 000.01.01.01.04 - Analytical Support | 000.01.01.01.04 | null | null | null | null | 000.01.01.01.04 - Analytical Support |
| 000.01.01.01.05 - Laboratory Engineering | 000.01.01.01.05 | null | null | null | null | 000.01.01.01.05 - Laboratory Engineering |
| 000.01.01.01.06 - Laboratory Maintenance | 000.01.01.01.06 | null | null | null | null | 000.01.01.01.06 - Laboratory Maintenance |
| 000.01.01.03 - Transition | 000.01.01.03 | null | null | null | 000.01.01.03 - Transition | null |
| 000.01.01.03.01 - Compliant Operations | 000.01.01.03.01 | null | null | null | null | 000.01.01.03.01 - Compliant Operations |
| 000.01.01.03.02 - Reliability | 000.01.01.03.02 | null | null | null | null | 000.01.01.03.02 - Reliability |
| 000.01.01.03.04 - Analytical Support | 000.01.01.03.04 | null | null | null | null | 000.01.01.03.04 - Analytical Support |
| 000.01.01.03.05 -Lab Engineering | 000.01.01.03.05 | null | null | null | null | 000.01.01.03.05 -Lab Engineering |
| 000.02 - External Review | 000.02 | null | 000.02 - Review and Support | null | null | null |
| 000.02.01 - External Review and Support A | 000.02.01 | null | null | 000.02.01 - External Review and Support A | null | null |
| 000.02.01.01 - External Review and Support B | 000.02.01.01 | null | null | null | 000.02.01.01 -External Review and Support B | null |
| 000.02.01.01.01 - External Review and Support ABCD | 000.02.01.01.01 | null | null | null | null | 000.02.01.01.01 - External Review and Support ABCD |
| 000.03 - Training | 000.03 | null | 000.03 - Training | null | null | null |
| 000.03.01 - Training & Review | 000.03.01 | null | null | 000.03.01 - Training & Review | null | null |
| 000.03.01.01 - Training A | 000.03.01.01 | null | null | null | 000.03.01.01 - Training A | null |
| 000.03.01.01.01 - Training Delivery | 000.03.01.01.01 | null | null | null | null | 000.03.01.01.01 - Training Delivery |
| 000.03.01.01.02 - Course Development | 000.03.01.01.02 | null | null | null | null | 000.03.01.01.02 - Course Development |
Thank you for your suggestion, however fill down doesn't work as it would associate a record of 000.01.01 to 000.02 in the Level 3 column. I figured out a solution, although it does require more steps and feels like more of a hack than anything else but it works. I added a custom column with the below formula:
let level3num = Text.BeforeDelimiter([Level 3],".",1),
level2num = Text.BeforeDelimiter([Level 2]," "),
compare = if Value.Compare(level3num,level2num)=0 then [Level 3] else nullin compare
I then created the heirarchy.
2 Replies
HI mvgust,
I think you can enter to query editor to use the 'fill down' feature to fill and replace these null values, then you can use these fields to create a hierarchy structure and use on your visuals.
Fill values in a column - Power Query | Microsoft DocsCreate Hierarchy in Power BI (tutorialgateway.org)
Regards,Xiaoxin Sheng
Thank you for your suggestion, however fill down doesn't work as it would associate a record of 000.01.01 to 000.02 in the Level 3 column. I figured out a solution, although it does require more steps and feels like more of a hack than anything else but it works. I added a custom column with the below formula:
let level3num = Text.BeforeDelimiter([Level 3],".",1),
level2num = Text.BeforeDelimiter([Level 2]," "),
compare = if Value.Compare(level3num,level2num)=0 then [Level 3] else nullin compare
I then created the heirarchy.
