topic Re: Custom Replacer.ReplaceText in Power Query

Custom Replacer.ReplaceText

Anonymous — Thu, 15 Jul 2021 12:29:04 GMT

I have no idea why this is not working. I am trying to add a custom holiday to my date table.

I get no error code. It acts like it has repaced the value but the cell is empty rather than XXXXXXX. When I sort the "Holiday" column desc the date 2021,4,2 is at the top. It just has no data in it.

#"Sorted Rows" = Table.Sort(#"Added Custom4",{{"Custom", Order.Descending}}),
#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows", each [Holiday], each if [Date] = #date(2021, 4, 2) then "XXXXXXXXXX" else [Holiday], Replacer.ReplaceText,{"Holiday"})
in
#"Replaced Value"

Re: Custom Replacer.ReplaceText

Anonymous — Thu, 15 Jul 2021 13:36:14 GMT

Try transforming the column--because keep in mind, if [Date] is type date, you can't replace it with the text "xxxxxxxx". If it's a text column., you can't use #date(2021,4,2" as a comparison to the text. Let's make it a text column so you can add the "XXXXXXXXXX".

#"Sorted Rows" = Table.Sort(#"Added Custom4",{{"Custom", Order.Descending}}),

Table.TransformColumnTypes(#"Sorted Rows", {{"Date", type text}}),
#"Replaced Value" = Table.TransformColumn(#"Added Custom4", {{"Holiday", each if [Date] = "4/2/2021" then "XXXXXXXXXX" else [Date]}})

--Nate

Re: Custom Replacer.ReplaceText

Anonymous — Thu, 15 Jul 2021 15:02:12 GMT

Thank you for your reply. The Date column is a date and the Holiday column is text. I am not trying to change the values in the Date column I am trying to change the values in the Holiday column.

Re: Custom Replacer.ReplaceText

Anonymous — Thu, 15 Jul 2021 15:07:41 GMT

Oh OK! In that case:

#"Sorted Rows" = Table.Sort(#"Added Custom4",{{"Custom", Order.Descending}}),

#"Replaced Value" = Table.TransformColumn(#"Added Custom4", {{"Holiday", each if [Date] = #date(2021,4,2) then "XXXXXXXXXX" else [Holiday]}})

--Nate

Re: Custom Replacer.ReplaceText

CNENFRNL — Thu, 15 Jul 2021 15:10:28 GMT

#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows", each [Holiday], each if [Date] = #date(2021, 4, 2) then "XXXXXXXXXX" else [Holiday], Replacer.ReplaceValue,{"Holiday"})

Re: Custom Replacer.ReplaceText

Anonymous — Thu, 15 Jul 2021 15:16:52 GMT

I am not sure why this worked because it appears to be the exact same thing as I was doing but it works. Thank You Very Much

Re: Custom Replacer.ReplaceText

Anonymous — Thu, 15 Jul 2021 15:18:06 GMT

I am not sure why this worked because it appears to be the exact same thing as I was doing except it worked. Thank You So much