Forum Discussion

mfikram's avatar
mfikram
Frequent Visitor
6 years ago
Solved

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

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

Tips and Tricks
  • Mariusz's avatar
    Mariusz
    6 years ago

    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

     

1 Reply

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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