Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Custom Replacer.ReplaceText

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"

 

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows", each [Holiday], each if [Date] = #date(2021, 4, 2) then "XXXXXXXXXX" else [Holiday], Replacer.ReplaceValue,{"Holiday"})

6 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows", each [Holiday], each if [Date] = #date(2021, 4, 2) then "XXXXXXXXXX" else [Holiday], Replacer.ReplaceValue,{"Holiday"})
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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