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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jameshoneywill
Frequent Visitor

Working with shared postcodes (different business in the same building)

Hi

 

I'm working with a set of data from a National body that shows every public medical practice in the country. 

 

I have a set of customer data with the practices which are already a customer. 

 

The only link I have between them is UK Postcode. And I have been merging the queries on this basis. 

 

On the Government data, there can be multiple practices in the same building and therefore records have the same postcode 

Therefore; 

When queries are merged it returns duplicates of my customer where the Gov data has multiple postcodes for different practices

 

Once I do a count of these in the report it would show (for example) that I have 5 customers when actually I only have 1 out of 5 at that postcode. 

 

I understand why this happens and that without a unique identifier per record (on each side of the merge) that this is what to expect. 

 

However, I wondered if anyone had any ideas on how to overcome this? I have unique reference numbers on each side, however, they are not a shared reference number between the sets of data, probably doesn't help but just thought I would mention it 

 

Many thanks

 

James

 

 

 

 

 

 

1 REPLY 1
speedramps
Super User
Super User

Hi James 

 

A key does what is says in on the tin.

If the key fits 5 door locks then you ned to try use soome of the other properties to try decide which is which.

 

If you cant and wnat to over come the duplicate key error, then that is easy !

Just add an index column to your query (1,2,3,4,5,6, etc) then add this as decimal to your key

eg key 77.3, 77.4, 77.5.

So you joint key (77) and unqiue keys (77.3, 77.4 and 77.5).

Hope that make sense.

 

Please click the thumbs up and accept as solution 😁

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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