Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
You will need to create the top table. I’m sure it can be done in Power Query. Can you post some sample data?
Could you clarify what you mean by "top table?" And by sample data, are you referring to hierarchy data or fact data?
sorry, I was referring to the 3 images in your OP. I was referring to the top one.
Ok that makes sense. So I would need to delete out unnecessary sub-hierarchies? Is there a way to do that other than by manually deleting out in excel? Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |