Search code examples
powerbidaxpowerquerypowerbi-desktoppowerbi-datasource

Power BI - custom Column to get Levels

I have created the cost account like this

And this is how it looks in Power BI when data imported.

I am not sure how to develop a custom column in Power BI that can return levels and at the same time maintain relationship.

For instance

  1. For SITE WORKS I get a value “Level 0” in custom column
  2. For Substructure I get a value “Level 1” in custom column [Child to SITE WORKS Parent]
  3. For Excavation in unclassified soil I get a value “Level 2” in custom column [Child to Substructure Parent]

The column is indicating the child belongs to which parent. If is 762, then it is a “Level 1” of to 762 “Level 1”

Can someone please help with this use case?

Much Appreciated.

Solution

  • Since you are working in Power BI, you might want to try this approach that uses DAX.

    This method could probably be better for two reasons:

    1. It is much more straightforward.
    2. It could automatically accommodate future level changes like the one where you added the NBA level. (With my earlier M-based approach, you'd have to edit the code if you added another level--that is certainly not optimal.)

    So, try this instead:

    Load your original table into Power BI. Then switch to the Data view to look at it.

    enter image description here

    Then click Column Tools > New Column and enter this into the formula box:

    Hierarchy = PATH(Sheet1[acct_id],Sheet1[parent_acct_id])

    Then click New Column again and enter this into the formula box:

    Level = "Level " & CONVERT(LEN(Sheet1[Hierarchy])-LEN(SUBSTITUTE(Sheet1[Hierarchy],"|","")),STRING)

    Then either hit the Enter key or click basically anywhere outside the formula box.

    That should do it. You should see something like this:

    enter image description here

    You might find this helpful. I did.