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" ...
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

     

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