Zip Code Heat Map

[Mandy22]

I have a table Companies that contains the fields company_name and zip_codes. zip_codes contains all the zip codes that they provide service in, separated by commas. Some companies have 300 zip codes.

I want to be able to display a map that shows the areas those companies provide service in, and have it shaded by the amount of companies serving in that area, heat map-style.

Example: Company A services zip codes 1, 2, 3, 4, 5. Company B services zip codes 3, 4, 5, 6, 7. The map should have color in zip codes 1-7, with 3-5 being darker than 1, 2, 6, and 7. 

I've tried doing this with the heat map feature on the bubble map visual, but in order to do this, I have to first separate all the zip codes into separate columns, giving me 300+ columns of zip codes. Going through each one and transforming them into Data Category Postal Code with Don't Summarize is slow and painful, and dragging each of them to Location takes 2 minutes to load each time. 

I've also tried using ArcGIS, but it doesn't recognize multiple zip codes in a value. 

Is there any other way to do this, or do I just have to buckle down and do it the first way that I tried?

[puffster]

I'm working on my first PowerBI app and have to do something similar -- I need to show the number of English-Learning students in each zip code area in Jefferson County, Kentucky.  I'm using PBi Version 2.1 and haven't found any mapping solutions that shade zip codes with an even "heat signature".  Just curious what map you used to accomplish your task?  

[puffster]

As a follow-up for anybody else looking for a similar solution, I ended up using Shape Map which is already provided in powerBI.  It's a preview feature though so you may have to pull it in through the options if it's not already showing in your Visualizations.  

[aakash_yad_pwr]

@puffster  Did you use any json for Zip code. Iam not able to map zip codes using shappe visual.

[puffster]

I Googled and found a site that had the latitude/longitude coordinates of the zips I needed.  I was able to download via csv file, then uploaded these into a SQL database that I then imported into PB.

The only other data involved is a second sql table -- no aggregations -- every record counts as "1" and is summed together in the shape file.  

 For mapping I put the Zip Code field from the Boroughs table into the Location and the # Students from the Enrollment table into Color Saturation.  And like magic I had a nice map!! 

[asubr]

There is no 'Data category' for zipcode, so what do you set it as? Postal code?

[puffster]

Yes, Postal code is what I set it to...sorry about that!! 

[asubr]

Thank you so much. I haven't had much luck with the shape map to show the zip codes yet. I will keep trying. 

[asubr]

Would you be able to share a demo pbix file?

[puffster]

I don't think I have the ability to -- I just tried to drag in a pbix and got the message "The file type (.pbix) is not supported...

[HotChilli · Accepted solution]

I just posted an answer here which should help.

https://community.powerbi.com/t5/Desktop/Parsing-comma-separated-values-for-slicer/m-p/780445#M37590...

If you get the data in the format:

Company  Zip

A               1

B               1

A               2

A               3

I think you can use the heat map to plot zip codes and Count of Company