Is it possible to normalise a file that has been denormalised but contains no relationships

[bpsearle]

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

[speedramps · Accepted 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 !

Click here to download the PBIX 

[bpsearle]

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