Hi Guru
Table.ReplaceValue(Source,
each "Date A in QTR",
each if "Date A"= "TBC" then "TBC" else "Date A",
Replacer.ReplaceValue,{"Date A in QTR"})
basically I only want to replace null to TBC. if "Date A"= "TBC" but couldn't get it work, where went wrong in the M Coding ?
Thanks
Hi Chris,
The null in your source data is not a true null value and it's part of the problem. First you need to remove the null from cell A11 in your file before loading to Power Query.
Then you can use this formula:
= Table.ReplaceValue(Source,
each [Date A in QTR],
each if [Date A]= "TBC" then "TBC" else [Date A],
Replacer.ReplaceValue,{"Date A in QTR"})
Mynda
Hi Mynda,
Thanks for pointing it out and notice that got to use square bracket for Date A and Date A in QTR, "Date A" and "Date A in QTR" will not work for each statement
but Replacer.ReplaceValue,{"Date A in QTR"}) is ok ?
not sure if we changed all to text will be ok ?
Hi Chris,
Square brackets declare column names. You cannot change them to double quotes in the 2nd and 3rd arguments of the formula. The last argument is a list of column names to search. This is entered as text, hence the double quotes.
Mynda
