Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi all, hope someone can help me with this one
I have a flat file that contains normalised data from 4 different tables but there are no relationships. The easiest way to explain this is to have a look at the example I've put together that shows the source and then the target tables. The source column 1 has an object identifier that indicates the target table. The relationship between the objects is in the order of the rows in the source. My idea was to use window functions to create the relational IDs on the source table and use that as the base to then be filtered to produce each of the target tables. My DAX on window functions is not good enough to figure this out and I'm not sure it is even possible. Any ideas or pointers would be greatly appreciated, thanks Brian.
Source data file with relational data flattened | |||||
PER | 75476 | Fred | Blogs | ||
ADR | 1 | The Street | Hadley | Lincs | |
ORD | 75TDJF | 01-Jan-21 | |||
DET | Chocolates | ||||
ORD | 87GDJH | 17-Jan-21 | |||
DET | Oranges | ||||
PER | 754665 | Jack | Sky | ||
ADR | The Firs | Long Road | Farley Green | Hants | |
ORD | 28ONDX | 07-Jan-21 | |||
DET | Milk | ||||
PER | 655465 | Olive | Flat | ||
ADR | 47 | New Lane | Truckbridge | Bucks | |
ORD | 12YEMF | 16-Jan-21 | |||
DET | Ham | ||||
ORD | 97DMEC | 29-Jan-21 | |||
DET | Eggs | ||||
DET | Chips | ||||
Person table | |||||
Person ID | Person ref | First name | Last name | ||
1 | 75476 | Fred | Blogs | ||
2 | 754665 | Jack | Sky | ||
3 | 655465 | Olive | Flat | ||
Address table | |||||
Address ID | Person ID | First line | Second line | Town | County |
1 | 1 | 1 | The Street | Hadley | Lincs |
2 | 2 | The Firs | Long Road | Farley Green | Hants |
3 | 3 | 47 | New Lane | Truckbridge | Bucks |
Order table | |||||
Order ID | Perosn ID | Order ref | Order date | ||
1 | 1 | 75TDJF | 01-Jan-21 | ||
2 | 1 | 87GDJH | 17-Jan-21 | ||
3 | 2 | 28ONDX | 07-Jan-21 | ||
4 | 3 | 12YEMF | 16-Jan-21 | ||
5 | 3 | 97DMEC | 29-Jan-21 | ||
Order detail table | |||||
Order detail ID | Order ID | Order item | |||
1 | 1 | Chocolates | |||
2 | 2 | Oranges | |||
3 | 3 | Milk | |||
4 | 4 | Ham | |||
5 | 5 | Eggs | |||
6 | 5 | Chips |
Solved! Go to Solution.
Hi bpsearle
Please consider this solution and smash that thumbs up button.
Download the attached pbix and look at the Power Query scripts.
I used conditions to add the person ref for "PER" and order ref for "ORD".
Then fill columns to populate the other rows,
Then added indexes and merges.
Volila .... you get your normalised tables !
Hi bpsearle
Please consider this solution and smash that thumbs up button.
Download the attached pbix and look at the Power Query scripts.
I used conditions to add the person ref for "PER" and order ref for "ORD".
Then fill columns to populate the other rows,
Then added indexes and merges.
Volila .... you get your normalised tables !
Hi @speedramps
This is phenomenal, I hadn't thought of doing this in Power Query as I really don't know it. There are some very nice functions that work a treat.
Thank you so much, Brian
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
73 | |
66 | |
60 |