I have a table in Power BI that lists assets hierarchically. Each row has a column with the asset code (_ms_code), a column with the parent code (_ms_prt_code), and several columns with asset information. I want to add a column that indicates the root asset for each row. Rows with ParentCode = 0 are the root assets.

Here is an example of my table structure:

I have a table in Power BI that lists assets hierarchically. Each row has a column with the asset code (_ms_code), a column with the parent code (_ms_prt_code), and several columns with asset information. I want to add a column that indicates the root asset for each row. Rows with ParentCode = 0 are the root assets.

Here is an example of my table structure:

I want to add a column RootAsset that shows the root asset code for each row. For example:

The asset code is in the column _ms_code and the parent code is in the column _ms_prt_code of the table WS_ASSET_1.

How can I achieve this in Power Query?

• powerbi
• powerbi-desktop

1 Answer 1

There is a very easy way to do this via DAX. For it to work, the root node(s) need to have null as their parent code. So in Power Query, replace the 0 with null for column _ms_prt_code.

Then in DAX, create a Calculated Column with:

AssetPath = PATH([_ms_code], [_ms_prt_code])

Then if you want the root Asset, create another Calculated Column with:

RootAsset = PATHITEM([AssetPath], 1)

Or you can do the above in one Calculated Column:

RootAsset = PATHITEM( PATH([_ms_code], [_ms_prt_code]) , 1)

See Understanding functions for parent-child hierarchies in DAX for more details.

There isn't a native function to do this in Power Query. You could look at this post Replicating PATH function of DAX in Power Query / M