Geocoding MS-Excel Addresses and using the geocoded data in PBI

279
2
Jump to solution
01-05-2024 06:25 AM
Labels (2)
Ed_
by MVP Regular Contributor
MVP Regular Contributor

I have MS-Excel data that has addresses but no lat long and I would like to map them as points. Now PBI ESRI does not support geocoding address fields so I am now thinking of bringing the data to ArcGIS Pro 3.1 and geocode it and then upload it to AGOL as a hosted feature layer and then bring the layer to PBI. But then the issue is that PBI does not show the layer attributes under Data so I can't use the layer's attributes for other visuals and filter and so the layer and the map will not be able to talk to other visuals in the report. 

So then another solution is to bring MS-Excel data to Pro and use Calculate Geometry to add the lat long columns and use the updated Excel data in PBI for mapping and other visuals in the dashboards.

 

Is there a better way to do this?

Tags (1)
0 Kudos
1 Solution

Accepted Solutions
Ian_Muehlenhaus
Esri Contributor
I may be wrong, but I think I have good news. 😊

I believe you can map locations by address in Power BI. However, you need to include all address data in a single field.

You can do that right in Power BI using DAX. (I had never used DAX before, but following the instructions in this blog did the trick for me. Took about 30 seconds with copy-paste.)

https://www.esri.com/arcgis-blog/products/power-bi/mapping/a-great-map-begins-with-better-accuracy/

I hope this works for you. Apologies if I misunderstood the question. More seasoned ArcGIS for Power BI users may be able to offer a better solution.

Best,

Ian
-------------------
Ian Muehlenhaus
Location Analytics

View solution in original post

2 Replies
Ian_Muehlenhaus
Esri Contributor
I may be wrong, but I think I have good news. 😊

I believe you can map locations by address in Power BI. However, you need to include all address data in a single field.

You can do that right in Power BI using DAX. (I had never used DAX before, but following the instructions in this blog did the trick for me. Took about 30 seconds with copy-paste.)

https://www.esri.com/arcgis-blog/products/power-bi/mapping/a-great-map-begins-with-better-accuracy/

I hope this works for you. Apologies if I misunderstood the question. More seasoned ArcGIS for Power BI users may be able to offer a better solution.

Best,

Ian
-------------------
Ian Muehlenhaus
Location Analytics
Ed_
by MVP Regular Contributor
MVP Regular Contributor

Thanks for the quick response bro and for sharing the link. Yeah the solution should work but for some reason I don't get any records displayed when I am using my enterprise account. However, when I use my AGOL account, I see 14 out of 49 records on the map. So, it could either be a data issue or a bug in the backend. 

I have created a ticket with ESRI customer services and have a meeting later today so hopefully the issue will get resolved. 

Happy New Year 🙂