Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

hierarchic database and recursive data display in powerBI

Hi!

So I'm currently working on some powerBI stuff, and I have a database table which is hierarchic, meaning I have something like this:

__id___Place______id_parent

  1. London_____null
  2. Paris________null
  3. Madrid_____null
  4. MyInc_________1
  5. Building1_____4
  6. Building2_____4
  7. Floor1________5
  8. Floor2________5

Equivalent to something like that:

  • London
    • MyInc
      • Building1
        • Floor1
        • Floor2
      • Building2
  • Paris
  • Madrid

 

I've found out I can use PATH() that gives me a list of id's from parent to my selected node, but how to go from (7, 5, 4, 1) to "Floor1 => Building1 => MyInc => London" string?

 

Thanks by advance for your support

Need Help

2 Replies

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    Do you want to achieve a string-like purpose through the path() function? For example, the following test I did. If you misunderstand what you mean, can you further clarify your needs, provide accurate test data and expected results, I will answer you as soon as possible.

    Column = PATH('Table'[Employee],'Table'[ReportingTo])

     

    You can also refer to related blogs:

    How to Retrieve Data Recursively with Power BI

    Recursive Functions in Power BI / Power Query — Powered Solutions


    Looking forward to your reply.

    Best Regards,
    Henry

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi!

      In my first atempt I was using the ids of the lines and was getting "1|2|6" and was trying to figure out how to make it look like what you've shown... but I can just make a new column with name depending on id, and then use it as recursive PATH!

      Thanks a lot!

       

      now is there a way to take that CEO|VP|AVP and make it look like "CEO -> VP -> AVP" ? it would be perfect!

      that would totally fit my need!

       

      Thanks again for your help