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 :
2) Min and Max Dates across the Rows of 4 Columns :
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
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.