Turn an imported hierarchy into a mapping table

[zahlenschubser]

Hi there!

I have another rather exotic problem.

In the reporting platform we use we have a hierarchial structure, but I cannot directly export the relationships, so I cannot build a BI reporting with it. What I can do is export it to excel, with a pattern like this:

The gradual "indentation" for each hierarchy layer is sadly only formatting in excel, not spaces, so I cannot use those.

Any ideas?

[BiNavPete]

Hi @zahlenschubser 

You need to have another column in your table for level in hierarchy.
Then in Power Query Add a custom column with Text.Repeat(" ",[Level]) & [Narrative]
This will add the number of spaces at the front of the text.

Bring that to Power BI and use it in your reports. You may have to switch off text wrapping in matrix or table visual for it to work.

Link to PBIX here.

You can check out further financial reporting ideas here.

Happy to help
Pete
Linked In: https://www.linkedin.com/in/pete-smith-955b73181

[zahlenschubser]

Thanks for that, but as noted above I have the tiny problem that I don't have spaces to use in the formula.

Importing the hierarchy table into BI query gives me just a flat list:

[BiNavPete]

Hi @zahlenschubser 

The Power Query formula Text.Repeat(" ",[Level]) & [Narrative] changes the narrative to have the same number of leading spaces as the level.

If you can share a PBIX or table with the lines in your image, I'll work it through

Pete
Linked In: https://www.linkedin.com/in/pete-smith-955b73181

[zahlenschubser]

If I had a level I wouldn't have this much trouble with it. =(

hierarchy pbix 

[BiNavPete]

Hi @zahlenschubser 

Can't download your PBIX? Think you need to share it with anyone for me to get to it

Pete
Linked In: https://www.linkedin.com/in/pete-smith-955b73181

[zahlenschubser]

Can you try it like this please? I can't seem to set the permissions to outside users.

https://ufile.io/uh9o1jy0

[BiNavPete]

Sorry can;t access that either. 

[zahlenschubser]

https://filebin.net/y72kv0gjrx4vvwti

[BiNavPete]

Hi @zahlenschubser 

I just downloaded your PBIX and looked at the table.
I can't see an easy way with just that column to add in the levels required for indenting. If there were just two tiers (the ledger codes with numbers at the beginning and the subtotal) then that would be straight forward in PQ, but that will definitely not be the case.

However it looks like the data is coming from a ERP system Chart of Accounts (CoA). The CoA table is likely to have some form of hierarchy coded into it. Maybe you could use that?

I specialise in Power BI finance and accounting solutions and don't use indentation at all, favouring colour coding and a custom visual from Profitbase. But that would still leave you with a mapping issue.

Sorry I couldn't provide a full answer here but hopefully some of the pointers will help.

Pete
Web: https://binavigation.com
Linked In: https://www.linkedin.com/in/pete-smith-955b73181

[zahlenschubser]

What I probably could do is convert the mapping in excel first, and then load that into PBI.

I tested the export there, and it comes in with a left indent, as shown in my initial post, but I couldn't figure out if there is a way to turn the left indent count into spaces.

[BiNavPete]

Hi @zahlenschubser 

Interesting idea. If you're familiar with UDFs in Excel VBA check this custom function out.

Pete

[zahlenschubser]

I managed to extract the hierarchy in a somewhat more workable format including the parent-child-relationship: https://filebin.net/go00tehzn6vx2iaz

But I don't have an indentation there, leading to this problem:

[zahlenschubser]

I made it work, but I am a little lost at how to proceed code-wise, as there are not always the same amount of accounts in one subgroup, and I don't know how to get from a sub-header to the next "higher" entry. I also added an index column, otherwise the sorting is acting up.

https://filebin.net/duot54m7i3ecgeuq

[BiNavPete]

Hi @zahlenschubser 

See PBIX Here.
This works through your hierarchy issue.
1. using the PQ formula Text.Repeat(" ",[Level]) & [LineDesc] to generate the indent
2. Switch off text Wrap in table or matrix values.

And yes you will need an index column in the table to sort by. Can't do this with sort by column in table view because you will have duplicate narratives.

I was a bit confused about message 13. If you are refering to the actual amounts from the accounts this is a huge topic which I would be happy to consult on.

But first check out my financials training videos here.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Happy to help!!

Pete
Web: https://binavigation.com
Linked In: https://www.linkedin.com/in/pete-smith-955b73181

[zahlenschubser]

Visually it works now, but what I would like to do is appply that hierarchy to the accounts now (and the exported values / other data on them).

But as the accounts don't have a 1:1 connection (income statement, NWC etc.) I cannot easily map that hierarchy to the accounts for that.

[BiNavPete]

HI @zahlenschubser 

As I said in earlier post the subject of mapping accounts/GL values to Power BI is a very complex subject and without a data model difficult to advise. But even then the DAX involved can be quite complex.

Please refer to earlier post to see links to power bi financials guidance.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Happy to help!!

Pete
Web: https://binavigation.com
Linked In: https://www.linkedin.com/in/pete-smith-955b73181