CODEWITHSUNDEEP
powerbipowerquerym
Fabrizio Ferrari
Fabrizio Ferrari

Reputation: 3

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!

Upvotes: 0

Views: 52

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

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

Upvotes: 0

horseyride
horseyride

Reputation: 21298

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

Upvotes: 0

Related Questions