Mapping of US States

[TePe]

Well, I would love to belive what we can read here: https://powerbi.microsoft.com/en-us/documentation/powerbi-service-tips-and-tricks-for-power-bi-map-v.... However I'm very much in doubt that this is reality...

So what I did: I downloaded the Consumer Complaints CSV (https://catalog.data.gov/dataset/consumer-complaint-database) and tried to map the data to the States. Unfortunately Power BI distributes the data across the world (i.e. it finds AA somewhere in Germany, no idea what it thinks what it schould mean). So I tried to give Power BI more context setting the State field to the category "State or Province". No change. So I created a calculated column which has the fixed value of "USA", set the category to "Country / Region" and put it before the State into the location field list. Well, now I see USA as a country but as soon as I drill down the States are again spread all over the world, so no change as well...

Any idea? Sometimes mapping seems for me more connected to "luck" than "knowledge"...

[v-yuezhe-msft · Accepted solution]

@TePe,

In your source data, your state is two-letter abbreviation, which can not be properly recognized in Power BI Desktop map visual.

To solve the above issue, firstly, in Power BI Desktop, click on “Get Date->Web”, enter http://www.50states.com/abbreviations.htm and import the following table to Power BI. Then in Query Editor, filter out incorrect values , remove duplicates(e.g. AE relates to multiple states) and you may need to change the value of US States column to correct state which is related to the unique AE.


Secondly, create relationship between your tables as follows.


Thirdly, set Data Category for the “US States” field on this new table to State or Province. And drag the “US States” field to Location to create Map visual.

Regards,
Lydia

[TePe]

Thanks for the feedback, this for sure is a solution for the problem (maybe it's also a good idea to filter on only the first 50 lines of the data, otherwise Power BI might have issues mapping "American Army" locations 😉 

So my point was a bit around the document I linked. So it seems that a hierarchy (Country -> State) is completely ignored by Power BI but the document states that it's considered as a help for Power BI to identify the location. NE i.e. was found in Switzerland although I had the country as the first information in the hierarchy... OK, lessons learned... Maybe worth some feedback to the author and/or the team 😉

Thomas