Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

replace null values with content from another column based on condition

I have 20 million rows loaded. I want to replace null values from Column B. The value to be replaced will depends on what value is indicated on the column a.   Example: For null values,   1 = W ...
Need Help
Tips and Tricks
Tutorial Requests
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous,

     

    Did you get any error messages? Or it returns wrong results? 

    Maybe this one.

    Result_Column =
IF (
    [Column B] = blank(),
    LOOKUPVALUE (
        'FixTable'[Letter],
        'FixTable'[Number], [Column A]
    ),
    [Column B]
)

     

     

    Best Regards,

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

Create a lookup table and then join your existing Table with the look up Table.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    apologies but i cannot figure out how should i make a look up table for this.

    i have 20 millions rows.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

       

      Since there are multiple null values, you will have to tell the software what should go in place of each such cell.  So there will have to be an input table of 2 columns with column 1 values from your existing table.  The second column should be the result you want.