Power Query Optimization: Error Handling and Transformation on Date Columns in minimal steps

[mfikram]

Hi,
I am trying to optimize the following into a single (or least) steps so that I can do the transform on the "target due date" and avoid creating a custom column altogether

    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Discovered Date", type date}, {"Target Due Date", type date}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Target Due Date", null}}),
    #"Added Custom" = Table.AddColumn(#"Replaced Errors", "Due Date", each if [Target Due Date] = null
then Date.AddDays([Discovered Date],30)
else [Target Due Date], type date),

 What would be the best way?

Thanks,

F

[Mariusz · Accepted solution]

Hi @mfikram 

Try this 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA3MAQiJR1kTqwOTM5I39AYKFehFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Discovered Date" = _t, #"Target Due Date" = _t]),
    #"Format Text To Date" = Table.TransformColumns(Source,{{"Target Due Date", each try Date.FromText( _ ) otherwise null, type date }}),
    #"Replaced Value" = Table.ReplaceValue(#"Format Text To Date",null, each Date.FromText( _[Discovered Date] ), Replacer.ReplaceValue,{"Target Due Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Discovered Date", type date}, {"Target Due Date", type date}})
in
    #"Changed Type"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn