Search code examples
powerbipowerqueryanalytics

Power BI - basic column refferencing


I am somehow unable to figure out column refferencing in an IF statement Power Query M.

I want to replace value in a column based on a condition evaluating string comparison on different column.

= Table.TransformColumns(
    #"Previous step",
    {"Col_1", each if [Col_2] = "ghi" then null else _}
)

"Col_1" - contains floating numbers Col_2 - contains 4x text value

=============================================================== In this example i want to replace the value if string in Col_2 equal to "ghi"

Data:
Col_1 | Col_2
0     | abc
12    | def
100   | ghi

Expected result:
Col_1 | Col_2
0     | abc
12    | def
null  | ghi

=============================================================== Please advise ...

I tried differently by trying, googling, even chatGPT 3.5 ... with a damn column refferencing.


Solution

  • Try this in powerquery. You need to do a replace

    let Source =#table({"Column1", "Column2"},{{0,"abc"},{12,"def"},{300,"ghi"}}),
    Update =Table.ReplaceValue(Source, each [Column1], each if [Column2] ="ghi" then null else [Column1] ,Replacer.ReplaceValue,{"Column1"})
    in Update
    

    enter image description here