Replace text in one column based on a date value in another column

Power Query
1

Hi folks,
I have 3 cols … [End of Support], [Code], [EOL]
The End of Support col is a date column. The other 2 are text cols.
Column [Code] only contains 2 texts. Say “A” and “B”
For any date < today in [End of Support] & [Code] = “A”, I wish to be able to update the [EOL] column with text “Y”
I tried combinations of Table.ReplaceValue without success.

TIA for your contributions to get me over this hurdle.

John

2

Hi @jmaikido,

Kindly find attached solution.

start:

image

Final output:
image

M-code:
= Table.ReplaceValue(#“Changed Type”,“N”,each if [End of Support] < DateTime.Date(DateTime.LocalNow()) and [Code] = “A” then “Y” else “N”,Replacer.ReplaceText,{“EOL”})

Kind Regards,
Hafiz

Power query solution.pbix (99.5 KB)

1 Like
3

Hi Hafiz,

Thanks for putting together your solution.
I now understand the syntax an also what you have demonstrated, however would you have any idea why, (in my case), the update (replace) did not work? Can I ask why you entered “N” as the second argument in the M-code string?

I used:
= Table.ReplaceValue(#“Changed Type”,“N”,each if [End of Support] = #date(2022, 2, 25) and [Code] = “A” then “Y” else “N”,Replacer.ReplaceText,{“EOL”})

I then filtered the [End of Support] column on 25/02/2022 and observed that the [EOL] column did not update!

4

Hi @jmaikido,

Why it did not work for you?
[Hafiz] I am really not sure as I think you did not attach file etc

Why I put “N” as 2nd argument?
[Hafiz] I replaced “N” with either “Y” or “N” based on the condition. Reason is that my initial value of the column was “N”. It was basically oldValue in the column which I wanted to replace.

I hope you understood the syntax now.

Kind Regards,
Hafiz

1 Like
5

Hi John,
@hafizsultan made an example code - but Hafiz has no idea what is in your column [EOL].
Quite likely that his code doesn’t work with your data simply because you do not have “N” in that column. And that is not his fault - if you have just one entry in EOL, simply adapt his solution accordingly.

Here is a solution if you have various/unknown entries in EOL (adapt the name of the previous step):

= Table.ReplaceValue(#"Changed Type",each [EOL],each if [End of Support] < DateTime.Date(DateTime.LocalNow()) and [Code] = "A" then "Y" else [EOL], Replacer.ReplaceValue,{"EOL"})

3 Likes
6

Hi @jmaikido, did the response provided by @hafizsultan and @Matthias help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

7

Hi Matthias,
Thank you for your explanation … your solution worked to achieve my desired result. Much appreciated.
Rgds, john

1 Like