Afternoon,
Hoping someone may be able to either 1) tell me I don't need to do anything or 2) point me in the right direction of sorting/cleaning an organized table in Power Query for a reporting model I'm creating.
Within the mode, I have a "Contracts" and "Purchase Order" facts table. Each table contains columns that identify associated organizational company structures (business sector (sector), operating group (OG), division (div), director (dir), and organizational code (org code); see the table below (shortened, actually contains 200+ org codes).
The Org Code is unique, so it can certainly be a key for that dim table. However, in my fact tables, the same code may show up hundreds to thousands of times, which causes issues when trying to report on the various organization structures (drill downs).
Should I be looking to unpivot the table in some fashion to create a unique identifier for, say, Sector, OG, Div, DIr, and tie that to the org code?
| Sector | OG | DIV | DIR | Org Code |
| SCT2G1 | OG61F1 | OG61D1 | OG61C1 | 33364 |
| SCT2G1 | OG61F1 | OG61D1 | OG61C1 | 33365 |
| SCT2G1 | OG61F1 | OG61D1 | OG61C1 | 33654 |
| SCT2G1 | OG63F1 | OG63D1 | OG63C1 | 33373 |
| SCT2G1 | OG63F1 | OG63D1 | OG63C1 | 33374 |
| SCT2G1 | OG63F1 | OG63D1 | OG63C1 | 33657 |
| SCT2G1 | OG63F1 | OG63D1 | OG63C1 | 33658 |
| SCT2G1 | OG63F1 | OG63D1 | OG63C1 | 33671 |
| SCT2G1 | OG63F1 | OG63D1 | OG63C1 | 33368 |
| SCT2G1 | OG63F1 | OG63D1 | OG63C1 | 33369 |
| SCT2G1 | OG63F1 | OG63D1 | OG63C1 | 33720 |
I'm having a hard time visualizing what you are asking. Can you please upload a small, anonymized sample of your workbook illustrating the issue and the desired outcome? My thought, without seeing it, is to remove duplicates; but I have a feeling I'm missing something important.
