"Validating" Addresses for map visual

[NickolajJessen]

Hi,

I have a list of let's say 100 addresses. 

I'm placing those 100 addresses on the map visual in power bi.

Problem is that i don't know if all addresses are valid and all 100 dots are being placed.

Let's say that only 99 dots were placed. I would never find out unless i sat down and clicked every address individually to see if a dot would appear corresponding to the selected project.

I've considered one option:

1. Export a list of the projects

2. Export a list of the projects after a selection has been made with the Lasso tool in maps

3. compare the two lists.

Setup: Cubes in being managed in Visual studio, Data warehouse in MSSQL

[NickolajJessen · Accepted solution]

Here's my conclusion to close the thread. 

Turn out having the entire address in one string is a shaky soloution.
1. I Tried using google and bing map api to validate addresses. Turns out to be a bad soloutiong as the API utilizes the search engine to help find the right address, while the MAP visual in Power BI doesn't no such thing - at least not to the same extent. So the API might say you got a correct address. Leading you to think that no actions needs to be made, but in reality the Power BI map can't place it with a dot.

2. The address might be correct, but the maps visualisation will place them incorrectly either ways. I found a thread from 2020 that had a problem of an address being shown correctly in desktop and incorrectly in Power BI Online. Here in 2023 i have the same problem. Correctly formatted address was placed in Norway in the Desktop Version, but was moved to ??France?? when uploaded to Power BI online. Having this type of inconsistency we've decided to go with lon/lat to do our map placements. 

[Anonymous]

Hi @NickolajJessen ,

You may try the “Show as a table” option. 

After clicking the option, it will display the map visual and a table visual with data points.

Hope that helps.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[NickolajJessen]

How does that help to see which are showing up and which not?

[christinepayton]

Typically you want to validate address data when it is input, not after the fact, but if that's not a possibility you'd want to do it on the database end before it gets to Power BI. There are lots of tools on the market for address validation. 

[NickolajJessen]

When it is input? As in the user manually checks in on the map service before entering it? Or some type of script somewhere runs every time an entry is added? I have a good amount of options to where i want to do it i believe. Maybe it's possible to make some kind of API call from MSSQL?

[christinepayton]

Doing it as it's input would be like what you see when you're purchasing things online sometimes, where as you type the address it pops up and you select the correct one. Some services, after you input it, will say "did you mean THIS address?" where it gives you the more "official" version of the same address (usually with the full zip code etc) and you can say "yes use that". 

There are also services you can integrate after the fact, e.g. as part of the ETL process as your data makes its way to SQL (e.g. as a step in a pipline in Databricks) that use an API. 

You could also use regular expressions in SQL yourself to validate that the address format is correct and flag some bad ones that way, as the free/cheap option (the rest of these will have a cost associated).