Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
bpsearle
Resolver II
Resolver II

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

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   
PER75476FredBlogs  
ADR1The StreetHadleyLincs 
ORD75TDJF01-Jan-21   
DETChocolates    
ORD87GDJH17-Jan-21   
DETOranges    
PER754665JackSky  
ADRThe FirsLong RoadFarley GreenHants 
ORD28ONDX07-Jan-21   
DETMilk    
PER655465OliveFlat  
ADR47New LaneTruckbridgeBucks 
ORD12YEMF16-Jan-21   
DETHam    
ORD97DMEC29-Jan-21   
DETEggs    
DETChips    
      
      
Person table     
Person IDPerson refFirst nameLast name  
175476FredBlogs  
2754665JackSky  
3655465OliveFlat  
      
Address table     
Address IDPerson IDFirst lineSecond lineTownCounty
111The StreetHadleyLincs
22The FirsLong RoadFarley GreenHants
3347New LaneTruckbridgeBucks
      
Order table     
Order IDPerosn IDOrder refOrder date  
1175TDJF01-Jan-21  
2187GDJH17-Jan-21  
3228ONDX07-Jan-21  
4312YEMF16-Jan-21  
5397DMEC29-Jan-21  
      
Order detail table    
Order detail IDOrder IDOrder item   
11Chocolates   
22Oranges   
33Milk   
44Ham   
55Eggs   
65Chips   
1 ACCEPTED SOLUTION
speedramps
Super User
Super User

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 

View solution in original post

2 REPLIES 2
speedramps
Super User
Super User

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 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.