cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
atavo Regular Visitor
Regular Visitor

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) and
  • where this is true, return the maximum date in a new column

Refer screenshot below:

 Example.png

1 ACCEPTED SOLUTION

Accepted Solutions
AnkitBI Established Member
Established Member

Re: Working with Multiple Date Columns in Power Query

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.

2 REPLIES 2
az38 Member
Member

Re: Working with Multiple Date Columns in Power Query

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"

AnkitBI Established Member
Established Member

Re: Working with Multiple Date Columns in Power Query

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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 111 members 1,597 guests
Please welcome our newest community members: