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

4 = E

 

and so on..

 


Please take note that null values are many. And the null values is not limited to two only. 

 

Just new to power bi desktop. please guide me. How should I do this?

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,

7 Replies

  • 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.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    Where are all the replacements? In other words, where is "1 = W 4 = E" from? It could be like this one.

     

    Result_Column =
IF (
    ISBLANK ( [Column B] ),
    LOOKUPVALUE (
        'replacementsTable'[result],
        'replacementsTable'[Column A], [Column A]
    ),
    [Column B]
)

     

    Best Regards,

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-jiascu-msft

       

      it will come from a separate table called "FixTable" and here is the sample data inside it. Data in the "FixTable" are only those numbers with blank letters.

       

       

      I tried using your code but it did not work. can you check what was wrong with it?

       

      Result_Column =
IF (
    ISBLANK ( [Column B] ),
    LOOKUPVALUE (
        'FixTable'[Letter],
        'FixTable'[Number], [Column A]
    ),
    [Column B]
)
      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        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,