ZIP Codes not matching up properly

[Hisham-Hussain]

Hello everyone, ive been given a dataset as technical assessment for a hiring process and im absolute stumped. I was given 6 csvs and a work book. The 6 workbooks were the revneue a store got from each respective country. The workbook cotained manufactuer data, product infromation and gegraphical data all as sepearte sheets. My problem stems with the geoprahical data, more specifically the zip code and i cant fiugre out how to deal with this. The countries in the data set are Canada, Germany, Japan, Mexico, Nigeria and the United States. When i plot all those separately im getting the right numbers as i cross referenced them using python. Even when I appened in power bi i get the same numbers. It all falls apart when i add a relationship to the geography table where in which all the ZIP codes are unique thus a one to many cardinality with my appended sales table. I have no idea whats going on and how to fix it.

After appending all 6 revenue data sets by countryafter creating relationship with geography table to get insight on cities and statesblanks data point as a tableappend and geography relationship

[v-kpoloju-msft · Accepted solution]

Hi @Hisham-Hussain,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @lbendlin, for your inputs on this issue.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.    

Your issue is happening because ZIP/Postal Codes are not globally unique. For example, the ZIP 12345 could exist in both the U.S. and Germany. If you join tables using only ZIP Code, Power BI may incorrectly match sales data to the wrong geographic record especially when combining multiple countries.

For resolve this issue For Append1 Table: Open Power Query Editor. Select the Append1 query. Add a Custom Column and paste this formula: Name it: NormalizedZip

= Text.PadStart(Text.From([Zip]), 5, "0")    

For Geography Table: Select the Geography query. Add a Custom Column and paste this:   Name it: NormalizedZip

= Text.Select([Zip], {"0".."9"})

This removes dashes and non-numeric characters (e.g., 492-8001 becomes 4928001). Now create relationship From Append1[NormalizedZip] → Geography[NormalizedZip], Many to one, Single direction.

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[Hisham-Hussain]

Kudos! Sorry for taking long to reply, very hectic week. But this worked perfectly, thanks so much!

[v-kpoloju-msft]

Hi @Hisham-Hussain,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @Hisham-Hussain,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[lbendlin]

the geography table where in which all the ZIP codes are unique

Zip codes? Unique across countries?  News to me...