Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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!
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
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!