Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Replace all dates in a column with a text

Hi Community,
I am not experienced in Power Querry and could not find a way to change all the dates in the below column with a single text "Completed". If the value starts with a number, it should be replaced with "Completed" . Any Idea?

 

Thank you in advance

 

 

Need Help

1 Reply

  • Peter_Beck's avatar
    Peter_Beck
    Icon for Resolver II rankResolver II
    3 years ago

    Hi 

     

    Here is a simple solution in "M" that seems to do what you want:

     

    let
    Source = Data,

    MyTable = Table.AddColumn(Source,"NewColumn", each
    if try Number.FromText(Text.At([Real Finish Date], 0)) is number otherwise false
    then "Completed" else [Real Finish Date])

    in
    MyTable

     

    It assumes you have a table called "Data". It will add a column to this data, using a test for each value to see if the first character can be successfully converted to a number. If it can be, then it replaces the value in the new column with "Completed", otherwise it keeps the existing value.

     

     

    Hope this helps!

     

    Cheers,

     

    Peter