Forum Discussion

atavo's avatar
atavo
Helper I
6 years ago
Solved

Working with Multiple Date Columns in Power Query

Hi   Kindly seek assistance on the following problem.   I have 4 date columns. For each row: check that dates in these columns fall from 1/1/2018 to 31/12/2018 (i.e. 1/1/2018<=x<=31/12/2018) an...
Need Help
  • AnkitBI's avatar
    AnkitBI
    6 years ago

    You can also use below code. Similar to other one but with a single step.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZYzLDcAwCMV24RyJQJrfLIj91wg0aVHVmx8yFoFMmBk5U4UEBadjM6xYHbtfCcl5giYxp/jgr3OUcUdK2JZ+M9sep8i/8fSvKMUHtsC+UXUB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StartDt1 = _t, StartDt2 = _t, StartDt3 = _t, StartDt4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDt1", type date}, {"StartDt2", type date}, {"StartDt3", type date}, {"StartDt4", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Max(List.Select(Record.FieldValues(_),each  _ >= Date.FromText("1/1/2018") and _ <= Date.FromText("31/12/2018"))))
in
    #"Added Custom"

    Thanks
    Ankit Jain

    Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

AnkitBI's avatar
AnkitBI
Solution Sage
6 years ago

You can also use below code. Similar to other one but with a single step.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZYzLDcAwCMV24RyJQJrfLIj91wg0aVHVmx8yFoFMmBk5U4UEBadjM6xYHbtfCcl5giYxp/jgr3OUcUdK2JZ+M9sep8i/8fSvKMUHtsC+UXUB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StartDt1 = _t, StartDt2 = _t, StartDt3 = _t, StartDt4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDt1", type date}, {"StartDt2", type date}, {"StartDt3", type date}, {"StartDt4", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Max(List.Select(Record.FieldValues(_),each  _ >= Date.FromText("1/1/2018") and _ <= Date.FromText("31/12/2018"))))
in
    #"Added Custom"

Thanks
Ankit Jain

Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.