how to replace all dates that are older then today by today

[DLU]

Dear community, 

I'm not sure if there is a solution for this in Powerquery but I have a salesorder table and the deliverydates aren't updated like they should. Therefore there are a lot of dates in the past. We want to use this data for forecast so we can't have dates in the past. Is there a custom column method that will replace each date that's before today by today? Thank you for the help!

Gr. Diana

[AntrikshSharma]

@DLU Here is another way to solve this:

let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "i45WMjIwMtI1BCIDpVgdMNdQ18BU18AMxjXWNTDRNTBE4hrpGlgqxcYCAA==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ #"Order Date" = _t ]
    ),
    ChangedType = Table.TransformColumnTypes ( Source, { { "Order Date", type date } } ),
    DateToday = Date.From ( DateTime.LocalNow() ),
    ReplacedValue = 
    Table.ReplaceValue (
        ChangedType,
        each if [Order Date] < DateToday then [Order Date] else false,
        each DateToday,
        Replacer.ReplaceValue,
        { "Order Date" }
    )
in
    ReplacedValue

[jaweher899]

Yes, you can replace the dates in the past with today's date using a custom column in Power Query. Here's how you can do it:

1. Open the sales order table in Power Query Editor.

2. Go to the "Add Column" tab and select "Conditional Column".

3. In the "Conditional Column" dialog, give the new column a name and select the "Delivery Date" column as the source column.

4. In the "If" condition, use the following expression to check if the delivery date is in the past: [Delivery Date] < #today()#

5. In the "Then" section, select the value "#today()#" as the replacement for the past delivery dates.

6. In the "Else" section, select the value "[Delivery Date]" to keep the delivery dates that are in the future.

7. Click "OK" to apply the changes.

This will create a new column in your sales order table with the corrected delivery dates. The past dates will be replaced with today's date and the future dates will remain unchanged. You can then use this new column in your forecasting and analysis.

[DLU]

Dear Jawaher899, 

Thank you for your reply but I can't seem to get it working. I don't see an option to put in such an expression. When I put it in the value field I get a yellow triangle with an error and if I put it in like this the column it just shows the text #today()#. What am I doing wrong? Thanks again for your help!

[AntrikshSharma]

@DLU By #today they meant a placeholder for the date.

You can create a step with the code Date.From ( DateTime.LocalNow() ) but you won't be able to use it in the Conditional Column dialog box so just enter any random date and then later use the step you created in the M code.