Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

I have a table with three columns Date, Customer, Product, Index in power query. Index…

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

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

    The expression uses a custom function called GetGroup to determine the group number based on the consecutive dates for each unique combination of Customer and Product. Here's the implementation of the GetGroup function:

    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
        GetGroup
    

    Replace the Source variable in both expressions with the name or reference of your source table, and the GroupAdded variable 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 Source table reference variable in both expressions to match your actual table name or reference.