Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
90 | |
89 | |
79 | |
70 | |
68 |
User | Count |
---|---|
222 | |
128 | |
117 | |
82 | |
77 |