powerbi - How to update a column's values in Power BI M Language - Stack Overflow

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!

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!

• powerbi
• powerquery
• m

Share Improve this question Follow asked Jan 2 at 14:52 Fabrizio FerrariFabrizio Ferrari 311 silver badge22 bronze badges 1

• FYI: Table.TransformColumns cannot reference other columns hence why it wouldn't work for you. Table.RepalceValue can as indicated in the answers below. – Sam Nseir Commented Jan 3 at 0:20

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

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"})

May be a little faster:

STEP_2 = Table.ReplaceValue(
        STEP_1,
        each [COL_B],
        each [COL_C],
        (a,b,c)as text=> if a=null 
                                then if b = "WW" then "WW"
                                        else if List.Contains({"XX","YY","ZZ"},c,Comparer.OrdinalIgnoreCase) 
                                            then Text.Upper(c)
                                        else "JJ"
                            else a,
        {"COL_A"})