Saturday, October 28, 2023

How to find Min and Max Dates across multiple Columns in Power Query

How to find Minimum and Maximum Dates across multiple Columns in Power Query
Scenario:
Suppose, we have the multiple Date Columns like [Act Date1], [Plan Date1], [Act Date2] and [Plan Date2] as follows.

Now we want to find the Min and Max Dates across the 4 Columns and rows as well. We can achieve this Scenario using the following Power Query:
1) Min and Max Dates across the Columns of 4 Columns :
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZHJDYAwDAR74Y1EvLmgFpT+2wABweOfHzNarfc8F9ts35SkZb1vdb8NZ3rvsT7CAYEyBKOgBAhyhqAgIDtDLhByEJCdIVcIJQh/tli0ga+BL84ji+9qga/Ooya+ZT3wzXm0xLPmTB/fnWdJPGvO9Am7C2yJ8gor+1AKNdF+zjTGBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Act Date1" = _t, #"Plan Date1" = _t, #"Act Date2" = _t, #"Plan Date2" = _t]),

ChangeType = Table.TransformColumnTypes(Source,{{"Act Date1", type date}, {"Plan Date1", type date}, {"Act Date2", type date}, {"Plan Date2", type date}}),

MinOfCols
Table.AddColumn(ChangeType, "MinDate_Cols", each List.Min(ChangeType[Act Date1] & ChangeType[Plan Date1] & ChangeType[Act Date2] & ChangeType[Plan Date2] )),

MaxOfCols
Table.AddColumn(MinOfCols, "MaxDate_Cols", each List.Max(MinOfCols[Act Date1] & MinOfCols[Plan Date1] & MinOfCols[Act Date2] & MinOfCols[Plan Date2]))

in
    MaxOfCols

Result:

2) Min and Max Dates across the Rows of 4 Columns :
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZHJDYAwDAR74Y1EvLmgFpT+2wABweOfHzNarfc8F9ts35SkZb1vdb8NZ3rvsT7CAYEyBKOgBAhyhqAgIDtDLhByEJCdIVcIJQh/tli0ga+BL84ji+9qga/Ooya+ZT3wzXm0xLPmTB/fnWdJPGvO9Am7C2yJ8gor+1AKNdF+zjTGBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Act Date1" = _t, #"Plan Date1" = _t, #"Act Date2" = _t, #"Plan Date2" = _t]),

ChangeType = Table.TransformColumnTypes(Source,{{"Act Date1", type date}, {"Plan Date1", type date}, {"Act Date2", type date}, {"Plan Date2", type date}}),

MinOfRows = Table.AddColumn(ChangeType, "MinDate_Rows", each List.Min({[Act Date1], [Plan Date1], [Act Date2], [Plan Date2]})),

MaxOfRows = Table.AddColumn(MinOfRows, "MaxDate_Rows", each List.Max({[Act Date1], [Plan Date1], [Act Date2], [Plan Date2]}))
in
    MaxOfRows

Result:

Thanks, TAMATAM

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.