Conditional Fill Down

[mvgust]

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

[mvgust · Accepted solution]

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 null

in compare

I then created the heirarchy.

[Anonymous]

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 Docs

Create Hierarchy in Power BI (tutorialgateway.org)
Regards,

Xiaoxin Sheng