How to structure lookup table hierarchy

Question

Hi I am trying to create a lookup table within Power BI.

The idea is that I should be able to retrieve my data at any of the four column levels below:

Financial Statement	Category	FS Account	Account #
Balance Sheet	Asset	Cash	110000
Balance Sheet	Asset	Cash	110020
Balance Sheet	Liability	Accounts Payable	220000
Balance Sheet	Liability	Accounts Payable	230000

The problem is, our database's hierarchy is structured more like this:

Column1	Column2	Column3	Column4	Column5	Column6	Column7
Balance Sheet	Asset	Cash	110000
Balance Sheet	Asset	Cash	110020
Balance Sheet	Liability	Accounts Payable	Accounts Payable Subcategory 1	Accounts Payable Subcategory 2	220000
Balance Sheet	Liability	Accounts Payable	Accounts Payable Subcategory 1	Accounts Payable Subcategory 2	Accounts Payable Subcategory 3	230000

Are there anyways to structure a lookup table in cases where the hierarchy levels of the original data don't align? Or would I have to manually go through the hierarchy and "normalize" like below?

Column1	Column2	Column3	Column4	Column5	Column6	Column7
Balance Sheet	Asset	Cash	Cash	Cash	Cash	110000
Balance Sheet	Asset	Cash	Cash	Cash	Cash	110020
Balance Sheet	Liability	Accounts Payable	Accounts Payable Subcategory 1	Accounts Payable Subcategory 2	Accounts Payable Subcategory 2	220000
Balance Sheet	Liability	Accounts Payable	Accounts Payable Subcategory 1	Accounts Payable Subcategory 2	Accounts Payable Subcategory 3	230000

If this is at all relevant, the data I'm working with is from Oracle HFM. I'm not using a direct query, but working with an extract file with base level data.

mattallington · Answer

You will need to create the top table. I’m sure it can be done in Power Query. Can you post some sample data?

altesv · Answer

Could you clarify what you mean by "top table?" And by sample data, are you referring to hierarchy data or fact data?

mattallington · Answer

sorry, I was referring&nbsp;to the 3 images in your OP.&nbsp; I was referring&nbsp;to the top one.

altesv · Answer

Ok that makes sense. So I would need to delete out unnecessary sub-hierarchies?&nbsp;Is there a way to do that other than by manually deleting out in excel? Thanks!

Forum Discussion

How to structure lookup table hierarchy

4 Replies

Related content

Power Query Structured lookup

Table structure

ELT JSON / Semi structured

Date hierarchy

Lookup help