I have a table with three columns Date, Customer, Product, Index in power query. Index…
- User
I have a table with three columns Date, Customer, Product, Index in power query. Index is index for unique combination of Customer and Product. Using M language provide code to get the column Group in which consecutive dates for unique combination of Customer and Product are placed to one group. For example, for Customer X and Product A1 dates 06.01.2023 to 06.02.2023 are placed to Group 1 and dates 06.05.2023 to 06.06.2023 are placed to Group 2. How to write the M expression to compare current row with next row and assigning 0 if the difference is 1 day and assigning 1 if the difference is more than 1 day?
- Assistant
To compare the current row with the next row and assign values based on the difference in dates, you can use the following M expression in Power Query:
let // Load the table Source = // Your source table here (replace this comment) GroupAdded = Table.AddColumn(Source, "Group", each GetGroup([Customer], [Product], [Date]), Int64.Type), Grouped = Table.Group(GroupAdded, {"Customer", "Product", "Group"}, {{"Dates", each [Date], type date}}), Result = Table.ExpandTableColumn(Grouped, "Dates", {"Date"}, {"Date"}), IndexAdded = Table.AddIndexColumn(Result, "Index", 0, 1, Int64.Type) in IndexAddedThe expression uses a custom function called
GetGroupto determine the group number based on the consecutive dates for each unique combination of Customer and Product. Here's the implementation of theGetGroupfunction:let GetGroup = (customer as text, product as text, date as date) => let Source = // Your source table here (replace this comment) Filtered = Table.SelectRows(Source, each [Customer] = customer and [Product] = product), Sorted = Table.Sort(Filtered,{{"Date", Order.Ascending}}), Grouped = Table.Group(Sorted, {"Customer", "Product"}, {{"GroupStart", each List.Min([Date]), type date}}), Merged = Table.NestedJoin(Sorted, {"Customer", "Product"}, Grouped, {"Customer", "Product"}, "Grouped", JoinKind.LeftOuter), Added = Table.AddColumn(Merged, "Group", each if Date.Day([Date]) - Date.Day([Grouped][GroupStart]{0}) <= 1 then 0 else 1), Result = Table.SelectColumns(Added, {"Group"}) in Result in GetGroupReplace the
Sourcevariable in both expressions with the name or reference of your source table, and theGroupAddedvariable in the main expression will contain the result with the "Group" column assigned to each unique combination of Customer, Product, and consecutive dates.Note: Make sure to modify the
Sourcetable reference variable in both expressions to match your actual table name or reference.