Power Query- earliest and lasted dates from multiple columns.

Mendy32

New Member
Joined
Dec 23, 2019
Messages
36
Office Version
  1. 2019
Platform
  1. Windows
I’m a started using PowerQuery and it has really made my working life much easier. Especially merging tables together...what I need help with is finding the earliest and latest dates from multiple columns 3-7. I have about 7200 rows..For example:

Column headers

1. name
2. Customer ID
3. IRCL date
4. IRFI date
5. iRMJ date
6. IRMC date
7. Tran date...

I know I have to add two additional columns to find the earliest date from 3-7 and lastest date 3-7. Also some dates will be missing from the rows cause not all customer went through columns 3-7.
Can you please help me...
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
any link to the shared excel file with representative example (with generic data if necessary)

or try
Group by Customer ID then Min for IRCL and Max for IRCL , the same for next date columns (Min, Max...Min, Max...)
but without knowing the whole structure I can't say more
 
Last edited:
Upvote 0
example
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Group = Table.Group(Source, {"Customer ID"}, {{"IRCL Min", each List.Min([IRCL]), type datetime}, {"IRCL Max", each List.Max([IRCL]), type datetime}, {"IRFI Min", each List.Min([IRFI]), type datetime}, {"IRFI Max", each List.Max([IRFI]), type datetime}, {"IRMJ Min", each List.Min([IRMJ]), type datetime}, {"IRMJ Max", each List.Max([IRMJ]), type datetime}, {"IRMC Min", each List.Min([IRMC]), type datetime}, {"IRMC Max", each List.Max([IRMC]), type datetime}, {"Train Min", each List.Min([Train]), type datetime}, {"Train Max", each List.Max([Train]), type datetime}})
in
    Group
Customer IDIRCLIRFIIRMJIRMCTrain
aa10/02/201012/02/201014/02/201016/02/201018/02/2010
aa01/02/202003/02/202005/02/202007/02/202009/02/2020
bb17/01/202018/01/202019/01/202020/01/202021/01/2020
bb20/02/202022/02/202024/02/202026/02/202028/02/2020
Customer IDIRCL MinIRCL MaxIRFI MinIRFI MaxIRMJ MinIRMJ MaxIRMC MinIRMC MaxTrain MinTrain Max
aa10/02/201001/02/202012/02/201003/02/202014/02/201005/02/202016/02/201007/02/202018/02/201009/02/2020
bb17/01/202020/02/202018/01/202022/02/202019/01/202024/02/202020/01/202026/02/202021/01/202028/02/2020

dates are from ceiling ?
 
Last edited:
Upvote 0
Thanks but what I need is the overall earliest dates and latest dates per row..for example aa earliest date is IRCL 2/10/20 AND LAtest date train 2/18/2020...

Let's say bb row earliest date is irmj 2/10 and latest irmc 2/18...
 
Upvote 0
so doesn't matter which column but must be earliest and latest from Customer ID row ?
Customer IDminmax
aa10/02/201009/02/2020
bb17/01/202028/02/2020

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Customer ID"}, "Attribute", "Value"),
    Group = Table.Group(Unpivot, {"Customer ID"}, {{"min", each List.Min([Value]), type datetime}, {"max", each List.Max([Value]), type datetime}}),
    Type = Table.TransformColumnTypes(Group,{{"min", type date}, {"max", type date}})
in
    Type
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top