Date difference by condition

[lipo_k]

Hi everyone! Need a bit of help from the Power query guru.

So,  I'm trying to get the last order and the previous order dates by each customer

For example, we have a table 

Date of order |  Customer     | Manager

------------------------------------------

01.01.2023     |  Customer 1  | Bob 

07.01.2023     |  Customer 2  | John

15.01.2023     |  Customer 1  | Bob

10.02.2023     |  Customer 3  | Ann

14.02.2023     |  Customer 1  | Bob

20.02.2023     |  Customer 2  | John

21.02.2023     |  Customer 2  | John

25.02.2023     |  Customer 1  | Bob

And as a result, need to get the table like 

Customer     | Manager | last order   | Previous order | Date Difference

-----------------------------------------------------------------------------

Customer 1  | Bob         | 25.02.2023 | 14.02.2023       |              11

Customer 2  | Tom         | 21.02.2023 | 20.02.2023       |              1

Customer 3  | Ann         | 10.02.2023 |      -                  |              0

Maybe someone knows how to get it... Its pretty simple to write in SQL, but in M I have no idea.

[ronrsnfld · Accepted solution]

You can Group by Customer and Manager, then extract the relevant data from each subgroup

In the code below, I used an Excel table as the data source, and had to do some trimming to remove the spaces from pasting your posted data, but you can use other data sources.

The below code is pasted into the Advanced Editor of Power Query, but the first four lines will need to be changed to reflect your actual data source.

let

//Change next four lines to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],
    #"Trim Column Names" = Table.RenameColumns(Source, List.Transform(Table.ColumnNames(Source), each {_, Text.Trim(_)})),
    #"Changed Type" = Table.TransformColumnTypes(#"Trim Column Names",{
        {"Date of order", type date }, {"Customer", type text}, {"Manager", type text}}, "en-GB"),
    #"Trim Data" = Table.TransformColumns(#"Changed Type", {
        {"Customer", Text.Trim},
        {"Manager",Text.Trim}}),

//Group by Customer and manager
//Then extract, from each subgroup, the relevant data
    #"Grouped Rows" = Table.Group(#"Trim Data", {"Customer", "Manager"}, {
        {"Recent Orders and Difference", (t)=>
            let
                Orders = List.MaxN(t[Date of order],2),

            //ensure there are two entries for each subgroup, even if only one date
                #"Last 2 Orders" = if List.Count(Orders) = 1 then Orders & {null} else Orders,
                
                diff = Duration.Days(#"Last 2 Orders"{0} - #"Last 2 Orders"{1}),
                #"As Records" = Record.FromList(#"Last 2 Orders" & {diff},{"last order", "Previous order", "Date difference"}) 
            in 
                #"As Records", type record}
        }),
    #"Expanded Recent Orders and Difference" = Table.ExpandRecordColumn(#"Grouped Rows", "Recent Orders and Difference", {"last order", "Previous order", "Date difference"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Recent Orders and Difference",{{"last order", type date}, {"Previous order", type date}, {"Date difference", Int64.Type}})
in
    #"Changed Type1"

. Results from your data

[ronrsnfld · Accepted solution]

You can Group by Customer and Manager, then extract the relevant data from each subgroup

In the code below, I used an Excel table as the data source, and had to do some trimming to remove the spaces from pasting your posted data, but you can use other data sources.

The below code is pasted into the Advanced Editor of Power Query, but the first four lines will need to be changed to reflect your actual data source.

let

//Change next four lines to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],
    #"Trim Column Names" = Table.RenameColumns(Source, List.Transform(Table.ColumnNames(Source), each {_, Text.Trim(_)})),
    #"Changed Type" = Table.TransformColumnTypes(#"Trim Column Names",{
        {"Date of order", type date }, {"Customer", type text}, {"Manager", type text}}, "en-GB"),
    #"Trim Data" = Table.TransformColumns(#"Changed Type", {
        {"Customer", Text.Trim},
        {"Manager",Text.Trim}}),

//Group by Customer and manager
//Then extract, from each subgroup, the relevant data
    #"Grouped Rows" = Table.Group(#"Trim Data", {"Customer", "Manager"}, {
        {"Recent Orders and Difference", (t)=>
            let
                Orders = List.MaxN(t[Date of order],2),

            //ensure there are two entries for each subgroup, even if only one date
                #"Last 2 Orders" = if List.Count(Orders) = 1 then Orders & {null} else Orders,
                
                diff = Duration.Days(#"Last 2 Orders"{0} - #"Last 2 Orders"{1}),
                #"As Records" = Record.FromList(#"Last 2 Orders" & {diff},{"last order", "Previous order", "Date difference"}) 
            in 
                #"As Records", type record}
        }),
    #"Expanded Recent Orders and Difference" = Table.ExpandRecordColumn(#"Grouped Rows", "Recent Orders and Difference", {"last order", "Previous order", "Date difference"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Recent Orders and Difference",{{"last order", type date}, {"Previous order", type date}, {"Date difference", Int64.Type}})
in
    #"Changed Type1"

. Results from your data

[flath]

Thank you, @ronrsnfld !

Your contribuition was very helpful!

[lipo_k · Accepted solution]

Thanks a lot, sir. Yeah, that's what I'm trying to do (about grouping by parameters). On SQL it's just a row "group by table11.customer, table11.manager", but on M I didn't know the correct functions.

Thx again!