cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Taff4Ever
Helper I
Helper I

Maps - Relationships with 2 files for UK postal code

Hi All

I have been fighting with maps for some time trying to get what I thought was a simple heat map to work based on UK postcodes.

 

After a bit of reading, I found a user who used the Office for National Stats and downloaded all post codes with the long\lat for each, which is a huge file, but now i want to match the post code from my data file with the national stats post cpde and need to find the best way to do this?!

 

Can you advise how best to achieve this please....then how best to show a count of people in a specific post code on to a map.....

 

Thank you

 

PBI - Relationship Post Code.png

 

2 ACCEPTED SOLUTIONS
davegwynedd
New Member

I've been using the various post code files from National Statistics and there are usually a number of different post code columns in the files. I'm assuming you've transformed the data in some way?

 

I can see 3 post codes in the NSPL_AUG_2019_UK file - headers: pcd, pcd2 and pcds. We had an issue with using the first two on our datasets but matched pretty well on pcds. Should be the same on the Nov release.

 

This assumes your post code data is clean - and that it's all in the same format - in this case pcds. If you've got a mixture, then you can always trim and clean the dataset in order to create a pcd version of the post code and link on that:

 

See some definitions below:

Three variations of formatting for each postcode are presented:

  • pcd: 7-character version of the postcode (e.g. 'BT1 1AA', 'BT486PL')
  • pcd2: 8-character version of the postcode (e.g. 'BT1  1AA', 'BT48 6PL')
  • pcds: one space between the district and sector-unit part of the postcode (e.g. 'BT1 1AA', 'BT48 6PL') - possibly the most common formatting of postcodes.

Source: http://data.nicva.org/dataset/ons-postcode-products

 

Hopefully that's some help, but let me know if you have any problems.

View solution in original post

v-shex-msft
Community Support
Community Support

HI @Taff4Ever ,

I think you need to add an address mapping table(mapping address and long/lat) as the bridge to link postcode table that contains long/lat information, then use the mapping table to link to your user table address field.

Tips and Tricks for Power BI Map visualizations#in-the-dataset-tips-to-improve-the-underlying-datase... 

Relationship in Power BI with Multiple Columns 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

HI @Taff4Ever ,

I think you need to add an address mapping table(mapping address and long/lat) as the bridge to link postcode table that contains long/lat information, then use the mapping table to link to your user table address field.

Tips and Tricks for Power BI Map visualizations#in-the-dataset-tips-to-improve-the-underlying-datase... 

Relationship in Power BI with Multiple Columns 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thank you both for the info, I have managed to get my data linked to the Stats data, and now Im using the correct field it seems to be working well....thank you.

 

Does PowerBi not support the UK in terms of counties, towns etc as all guides i can find are based on zip code or states which look very easy to manipulate!?

 

Thanks again

davegwynedd
New Member

I've been using the various post code files from National Statistics and there are usually a number of different post code columns in the files. I'm assuming you've transformed the data in some way?

 

I can see 3 post codes in the NSPL_AUG_2019_UK file - headers: pcd, pcd2 and pcds. We had an issue with using the first two on our datasets but matched pretty well on pcds. Should be the same on the Nov release.

 

This assumes your post code data is clean - and that it's all in the same format - in this case pcds. If you've got a mixture, then you can always trim and clean the dataset in order to create a pcd version of the post code and link on that:

 

See some definitions below:

Three variations of formatting for each postcode are presented:

  • pcd: 7-character version of the postcode (e.g. 'BT1 1AA', 'BT486PL')
  • pcd2: 8-character version of the postcode (e.g. 'BT1  1AA', 'BT48 6PL')
  • pcds: one space between the district and sector-unit part of the postcode (e.g. 'BT1 1AA', 'BT48 6PL') - possibly the most common formatting of postcodes.

Source: http://data.nicva.org/dataset/ons-postcode-products

 

Hopefully that's some help, but let me know if you have any problems.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors