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
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
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 😁
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |