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. If you wish, please share your genuine Feedback or comments only related to this Blog Posts. It is my humble request that, please do not post any Spam comments or Advertising kind of comments, which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose we have a sample Sales data, which is stored with Monthly Snapsh...

Popular Posts from this Blog