A weekend challenge
I hope you are enjoying your second weekend of 2019. I was helping a friend recently figuring out the following data challenge:
You have a table of Backlog items with three levels of hierarchies (Scenario, Feature and Backlog Item). In each row, the source data includes the unique ID of the lowest level, but has blank values in the columns that represent the higher levels, as shown at the left table.
The format on the left is not ideal for analytics. You may think that it is not common to find such format. Unfortunately, it is a common format. For example, here is a screenshot from an output of Team Foundation Add-In in Excel (source is here).
Can you fill down the table using Power Query in Excel or Power BI to reach the format of the right table above? Can you take your solution one step further, and write it in a way it will support dynamic number of levels and different header names? (For example: Continent, Country, Country, City, Street).
You can download the source table here. The solutions will be published soon.
Update (Jan 14, 2019): Thank you for the solutions you proposed below. Here is a solution that receives the table and the number of hierarchies in and uses recursions to resolve the challenge, without knowing in advance how many levels of hierarchies or column names are used. In the next blog post, I will describe it in details along with the best queries that were shared below.