Power Query- earliest and lasted dates from multiple columns.

[Mendy32]

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...

 

[sandy666]

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

 

[sandy666]

example

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 ID	IRCL	IRFI	IRMJ	IRMC	Train
aa	10/02/2010	12/02/2010	14/02/2010	16/02/2010	18/02/2010
aa	01/02/2020	03/02/2020	05/02/2020	07/02/2020	09/02/2020
bb	17/01/2020	18/01/2020	19/01/2020	20/01/2020	21/01/2020
bb	20/02/2020	22/02/2020	24/02/2020	26/02/2020	28/02/2020
Customer ID	IRCL Min	IRCL Max	IRFI Min	IRFI Max	IRMJ Min	IRMJ Max	IRMC Min	IRMC Max	Train Min	Train Max
aa	10/02/2010	01/02/2020	12/02/2010	03/02/2020	14/02/2010	05/02/2020	16/02/2010	07/02/2020	18/02/2010	09/02/2020
bb	17/01/2020	20/02/2020	18/01/2020	22/02/2020	19/01/2020	24/02/2020	20/01/2020	26/02/2020	21/01/2020	28/02/2020

dates are from ceiling ?

 

[Mendy32]

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...

 

[sandy666]

so doesn't matter which column but must be earliest and latest from Customer ID row ?

Customer ID	min	max
aa	10/02/2010	09/02/2020
bb	17/01/2020	28/02/2020

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

 

[Mendy32]

Yes...not all columns will have dates

 

[sandy666]

refresh thread and see post#5

 

[Mendy32]

Perfect! How?

 

[sandy666]

refresh thread again and see post#5

 

[Mendy32]

Awesome...thank you!