powerbipowerquerym

How to update a column's values in Power BI M Language

I am struggling with the following.

I have uploaded a dataset in Power BI. Now, I would like to update a column's (COL_A) values according to a case-when (or if-then) logic.

I am trying the following, but it does not work:

STEP_2= Table.TransformColumns(STEP_1, {"COL_A", each 
        if [COL_A] is null then
            if [COL_B] = "WW" then "WW"
            else if Text.Upper([COL_C]) = "XX" then "XX"
            else if Text.Upper([COL_C]) = "YY" then "YY"
            else if Text.Upper([COL_C]) = "ZZ" then "ZZ"
            else "JJ"
        else [COL_A]
    })

could anybody please help? Thank you in advance!

Solution

  • How about

    STEP_2=  Table.ReplaceValue(STEP_1,  each [COL_A], each 
if [COL_A] = null then
        if [COL_B] = "WW" then "WW"
        else if Text.Upper([COL_C]) = "XX" then "XX"
        else if Text.Upper([COL_C]) = "YY" then "YY"
        else if Text.Upper([COL_C]) = "ZZ" then "ZZ"
        else "JJ"
    else [COL_A]
,Replacer.ReplaceValue,{"COL_A"})