Working with NA data

[RecycleBin_Rob]

Hi,

I'm still learning and did some research into the topic. It looks like Power BI doesn't have an ifna function like excel and uses lookupvalue. I looked at those threads, but couldn't figure out how to apply it to my situation.

I'm working with City data that has a quadrant system (NW, SW, NE, and SE). The source has two sets of quadrant data. Normally I would assume they are the same and choose one, but life is just never that easy.

Note, this is a sample and not indicative of how many N/A's are in each column.

Priority

I'm assuming I will need to add a new column, but I don't know how to write the formula. It would compare "quadrant" and "geo_area_quadrant".

1. If the two are equal, then it doesn't matter which column it pulls from.
2. If either is N/A, choose the column with data that isn't N/A.

Add on option A (if possible)

I'm not sure if this is possible, well an easy way for the following ask... this is an issue with the raw data. In the instances where both columns are N/A, is it possible to extract data from an address column? If so, could it be combined with the column in my priority ask above, or would it be an additional column?

Thanks in advance!

[Syk · Accepted solution]

Create a custom column and try this...

if List.Contains({"NW","SE","NE","SW"},[quadrant]) 
then [quadrant]
else if List.Contains({"NW","SE","NE","SW"},[geo_area_quadrant]) 
then [geo_area_quadrant]
else if Text.Contains([display_address],"NW") then "NW"
else if Text.Contains([display_address],"SW") then "SW"
else if Text.Contains([display_address],"NE") then "NE"
else if Text.Contains([display_address],"SE") then "SE"
else "who knows"

[sevenhills · Accepted solution]

DAX for add new column:

Derived Quadrant = 
SWITCH( TRUE(), 
    'Table'[quadrant] <> "N/A", 'Table'[quadrant],
    'Table'[geo_area_quadrant] <> "N/A", 'Table'[geo_area_quadrant],
    'Table'[quadrant] = "N/A" && 'Table'[geo_area_quadrant] = "N/A" , 
          SWITCH( TRUE(),
              CONTAINSSTRING('Table'[display_address], "SE"), "SE",
              CONTAINSSTRING('Table'[display_address], "NE"), "NE",
              CONTAINSSTRING('Table'[display_address], "NW"), "NW",
              CONTAINSSTRING('Table'[display_address], "SW"), "SW"
              -- ... add more conditions of your needs
          , "N/A")
    , "N/A" -- you can chose if any of the above conditions do not meet, as default value!
    ) 

[Syk · Accepted solution]

Create a custom column and try this...

if List.Contains({"NW","SE","NE","SW"},[quadrant]) 
then [quadrant]
else if List.Contains({"NW","SE","NE","SW"},[geo_area_quadrant]) 
then [geo_area_quadrant]
else if Text.Contains([display_address],"NW") then "NW"
else if Text.Contains([display_address],"SW") then "SW"
else if Text.Contains([display_address],"NE") then "NE"
else if Text.Contains([display_address],"SE") then "SE"
else "who knows"