Forum Discussion

alwang's avatar
alwang
Frequent Visitor
1 year ago

Bug: Power Query ReplaceValue Bug related to replacing String with null

Hi, I think I have encountered a bug in the ReplaceValue function, where I applied conditional replacinng values:

 

For column [Assessment], I want to do if its value is "N/A", then replace it with null, if its value is ..., then .... But I noticed that replacing "N/A"  with null doesn't work. It works if I replace "N/A" with any other values. This is so strange. But make it even more strange, the same code works on another data table! 

 

1st screenshot is the step before the replaceValue, and 2nd is the ReplaceValue step, and 3rd screenshot is when I change null to "test". You can see that only when I use   if [Assessment]= "N/A" then null     it doesn't work.

 

anyone can pin down a coding error? or it's really a Power Query bug?

 

Thanks

General Comment
Need Help

3 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    1 year ago

    you try changing N/A to null first

    = Table.ReplaceValue(Source,"N/A",null,Replacer.ReplaceValue,{"Assessment"})
    • alwang's avatar
      alwang
      Frequent Visitor
      1 year ago

      Thanks. I know. But my question is that why my code works one table, but not on this table

      • Ahmedx's avatar
        Ahmedx
        Super User
        1 year ago

        rewrite the code like this and it will work

        = Table.ReplaceValue(
    #"Changed Type", (x)=>x, (x)=>x , 
              (x,y,z)=> if x = "N/A" then null else if  x = "Blouse" or x ="Kosovorotka" then "AAAA" else x,{"Assessment"})