# Power Query- earliest and lasted dates from multiple columns.



## Mendy32 (Feb 21, 2020)

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 (Feb 21, 2020)

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 (Feb 21, 2020)

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 IDIRCLIRFIIRMJIRMCTrainaa10/02/201012/02/201014/02/201016/02/201018/02/2010aa01/02/202003/02/202005/02/202007/02/202009/02/2020bb17/01/202018/01/202019/01/202020/01/202021/01/2020bb20/02/202022/02/202024/02/202026/02/202028/02/2020Customer IDIRCL MinIRCL MaxIRFI MinIRFI MaxIRMJ MinIRMJ MaxIRMC MinIRMC MaxTrain MinTrain Maxaa10/02/201001/02/202012/02/201003/02/202014/02/201005/02/202016/02/201007/02/202018/02/201009/02/2020bb17/01/202020/02/202018/01/202022/02/202019/01/202024/02/202020/01/202026/02/202021/01/202028/02/2020
dates are from ceiling  ?


----------



## Mendy32 (Feb 21, 2020)

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 (Feb 21, 2020)

so doesn't matter which column but must be earliest and latest from Customer ID row ?
Customer IDminmaxaa10/02/201009/02/2020bb17/01/202028/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 (Feb 21, 2020)

Yes...not all columns will have dates


----------



## sandy666 (Feb 21, 2020)

refresh thread and see post#5


----------



## Mendy32 (Feb 21, 2020)

Perfect! How?


----------



## sandy666 (Feb 21, 2020)

refresh thread again and see post#5


----------



## Mendy32 (Feb 21, 2020)

Awesome...thank you!


----------



## Mendy32 (Feb 21, 2020)

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 (Feb 21, 2020)

You are most welcome 

Customer IDIRCLIRFIIRMJIRMCTrainaa12/02/201014/02/201016/02/2010aa01/02/202003/02/202007/02/202009/02/2020bb17/01/202019/01/202020/01/202021/01/2020bb20/02/202022/02/202024/02/202028/02/2020Customer IDEarliest dateLatest dateaa12/02/201009/02/2020bb17/01/202028/02/2020

```
// Table3
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Customer ID"}, "Attribute", "Value"),
    Group = Table.Group(Unpivot, {"Customer ID"}, {{"Earliest date", each List.Min([Value]), type datetime}, {"Latest date", each List.Max([Value]), type datetime}}),
    #"Changed Type" = Table.TransformColumnTypes(Group,{{"Earliest date", type date}, {"Latest date", type date}})
in
    #"Changed Type"
```


----------

