Bug In Table.ReplaceValue when target Column field is blank

[joris-kempen]

I want to conditionally replace a Field column value based on a IF statement on other column.

For some reason i couldnt get it to work, which i later found out has to do with a empty value in the target colum.

Test case with a blank Power BI document:

Create a Table using Data Entry with this format:

Code  Reference

HSPAHAPAN VW

HSPHAPAN     (notice a empty field for Reference)

HAMUMC VW003570

So the Reference fields are wrong for 2 rows are Wrong. If Code == HSPHAPAN then Reference should be VW003569

So I used thise code for transformation:

= Table.ReplaceValue(
#"Waarde vervangen",
each [Reference], //old field value
each if [Code] = "HSPHAPAN" then "VW003569" else [Reference] , // new field value based on IF statement on different Column. If no Match, use the old field value
Replacer.ReplaceText,{"Reference"})

This only work for the HSPHAPAN rows with some content in the Reference field! So result became:

Code Reference

HSPHAPAN VW003569   (The VW transformed into VW003569, correct)

HSPHAPAN             (nothing is done here, wrong!)

HAMUMC VW003570  (Data is untouched, correct)

I'm rather new to Power BI / M / Dax, so maybe i'm missing something?

[joris-kempen]

Hmm really had the idea, i used both options without getting the correct results. But now seems correct with ReplaceValue! Thanks

[Vijay_A_Verma · Accepted solution]

Your code is correct. Just change Replacer.ReplaceText to Replacer.ReplaceValue