Forum Discussion

RecycleBin_Rob's avatar
RecycleBin_Rob
Frequent Visitor
2 years ago
Solved

Working with NA data

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!

"beginner asks Help"
"Power BI"
Tutorial Requests
  • Syk's avatar
    Syk
    2 years ago

    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's avatar
    sevenhills
    2 years ago

    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!
    )

     

2 Replies

  • Syk's avatar
    Syk
    Resident Rockstar
    2 years ago

    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's avatar
    sevenhills
    Super User
    2 years ago

    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!
    )