Replace latitude and longitude data with NULL if they are out of a certain range

[nico_333]

After reading many post from this community it's my time to ask for a help.

I have a table with half milions records that cointains lat e long data of places around the world divided by projects. I want to display only the data that falls in a certain range on a map. And replace the other value with "null" so they will not appear on the map.

Each project has its own lat long min and max data. 

Can someone help me to understand wich step shall I add to power query in lat e long field when loading the data from source?

Thank you

[v-echaithra]

Hi @nico_333 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-echaithra · Accepted solution]

Hi @nico_333 ,

Thanks for the update.
Open Power Query:

In Power BI Desktop, go to Home > Transform data
Select the latitude column, and then click "Add Column" > "Custom Column"

column_latitude
= if [Latitude] <> null and [Latitude] >= -90 and [Latitude] <= 90 then [Latitude] else "null"

column_longitude
= if [Longitude] <> null and [Longitude] >= -90 and [Longitude] <= 90 then [Longitude] else "null"

If your requirement is to insert a blank value, you should use null (without quotes) instead of the string "null". Using  "null" will insert a text value, whereas null represents a true blank in Power BI or Power Query.

Hope this helps

[v-echaithra]

Hi @nico_333 ,

I hope the information provided is helpful. I wanted to check whether you were able to resolve the issue with the provided solutions. Please let us know if you need any further assistance.

Thank you.

[v-echaithra]

Hi @nico_333 ,

Thank you @shashiPaul1570_  for your inputs.

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.

[shashiPaul1570_]

Hi  @nico_333 

Since you want to ensure that latitude and longitude values outside of each project’s valid range are replaced with NULL, so they don’t appear on the map. Here’s the approach you can follow in Power Query:

Create/Use a Project Boundaries Table

This table should contain Project, Lat_Min, Lat_Max, Long_Min, and Long_Max.

Example:

Project | Lat_Min | Lat_Max | Long_Min | Long_Max
      P1 | 10 | 20 | 40 | 60 P2 | -30 | -10 | 100 | 120
Merge with Your Main Data Table

In Power Query, merge your fact table (with millions of records) to the Project Boundaries table on the Project column.

This gives each record its valid min/max values.

Add Conditional Columns for Cleaning Latitude/Longitude

For Latitude

if [Latitude] >= [Lat_Min] and [Latitude] <= [Lat_Max]
then [Latitude] else null
For Longitude

if [Longitude] >= [Long_Min] and [Longitude] <= [Long_Max]
then [Longitude] else null
This ensures that values outside the valid range are replaced with null.

Remove Helper Columns (min/max columns if not required further).

Load Back to Power BI — The map will now plot only valid points, ignoring nulls.

This way, you maintain data integrity while ensuring visuals are clean and relevant.

Hope this helps resolve your issue! 
If this solution worked for you, please give it a Kudos 👍 and mark it as Accepted Solution ✅ so it can help others in the community too.

Thanks
Shashi Paul 

[nico_333]

Thank you for your eplanation.

I succeffully marge the queries however when it cames to define the conditional columns I got stacked because I need to define an output for each condition

nico

[v-echaithra]

Hi @nico_333 ,

In Power Query’s Conditional Column dialog, you need to specify what should happen when the condition is met and when it’s not. Here's how to structure it:

For Latitude:

If [Latitude] >= [Lat_Min] And [Latitude] <= [Lat_Max] → Then: [Latitude]
Else: null

For Longitude:
If [Longitude] >= [Long_Min] And [Longitude] <= [Long_Max] → Then: [Longitude]
Else: null

[nico_333]

Thank you for your time. 

I can't understand how to implement the AND operator.

As you can see i don't have an option to select the operator. I have to separate the conditions and define output for each condition.

Nico

Should I need additional columns?