Forum Discussion
Trying to replace errors and replace values dynamically instead of entering static value
Hi,
I have a date called Lease Date.
It has some errors and blanks
So I had used Replaced Errors and Replcaed Values functions from transformation in power query and replaced such values with a static date value.
Now I want that the replcaement should take place using dynamic values such that it should be Funded_Date - 16 days
I'm trying to edit it in advanced editor and here is the error i get.
I'm not a pro at m code.
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Lease start", Date.AddDays([Funded date],-16)}}),
#"Replaced Value" = Table.ReplaceValue(#"Replaced Errors",null,Date.AddDays([Funded date],-16),Replacer.ReplaceValue,{"Lease start"})
in
#"Replaced Value"
Error:
klehar Another thought on this. ReplaceErrorValues but use a date like 12/30/2100. Add a conditional column and if that is the value, replace with your AddDays logic, otherwise, just use the existing date. Remove the original column and you are good.
8 Replies
klehar Well, you need 2 lists for ReplaceErrorValues: Table.ReplaceErrorValues - PowerQuery M | Microsoft Docs
Looks like a similar issue perhaps for ReplaceValues: Table.ReplaceValue - PowerQuery M | Microsoft Docs
Hi Greg_Deckler , I'm afraid im not very good at M code. Can you help me explain how this can be done
klehar I'm thinking:
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {"Lease start", Date.AddDays([Funded date],-16)}), #"Replaced Value" = Table.ReplaceValue(#"Replaced Errors",null,Date.AddDays([Funded date],-16),Replacer.ReplaceValue,{"Lease start"}) in #"Replaced Value"First line changed dropping the extra { } items. If this doesn't work, I will try to mock something up.
Greg_Deckler , this doesnt work, can you mock somethign up
klehar Another thought on this. ReplaceErrorValues but use a date like 12/30/2100. Add a conditional column and if that is the value, replace with your AddDays logic, otherwise, just use the existing date. Remove the original column and you are good.
