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.

az38's avatar
az38
Community Champion
6 years ago

Hi atavo 

 

try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY7LDcAgDEN34YyUDw2BWRD7r1GiEIp6s+HZ8RiJABkYSVJOBbCbrksLoJhWe18Qmelp5mFYMcc/LKjmVeVL8D6i/iNBKaCeBAGxm33kucrk0vXSeiWiQL25HQyBzrAYvIbNFw==", 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.MaxN({[StartDt1],[StartDt2],[StartDt3],[StartDt4]},1, each _ <= Date.FromText("31.12.2018") and _ >= Date.FromText("01.01.2018"))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Custom]>=Date.FromText("01.01.2018") and [Custom] <= Date.FromText("31.12.2018") then [Custom] else null)
in
    #"Added Custom1"

pay attention to steps  #"Added Custom", #"Expanded Custom" and #"Added Custom1"