Power Query - Find the days difference from a Service date to another by a Customer in a table

Question

Hi, i'm working on a data set where i have customers (Card#) using specific services on a particular date. For each service used, i need to find if there is a repeat within 30 days. Below is the sample data set. (its only one table)
Hope my query is clear. I'm at a beginner level and new to the community as well.

Answer 1

@Zamjaf

Assuming data in Table1

1/ Setup a seperate query, name it i.e. fxComputeRepeatedService and give it the following code:

(inputTable as table, maxDaysDiff as number) as table =>  
let  
    fxShiftColumnByOneRow = (inputTable as table, ColumnToShift as text, optional NewColumnName as nullable text) as table =>  
    // By Imke Feldman @ https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/  
    let  
        Source = inputTable,  
        NewName = if NewColumnName = null then "Next_" & ColumnToShift else NewColumnName,  
        ShiftedList = List.RemoveFirstN(Table.Column(Source, ColumnToShift), 1) & {null},  
        SourceColumnsAsLists_Concat_ShiftedList = Table.ToColumns(Source) & {ShiftedList},  
        NewTableFromAboveLists = Table.FromColumns(SourceColumnsAsLists_Concat_ShiftedList, Table.ColumnNames(Source) & {NewName}),  
        ChangedType = Table.TransformColumnTypes(NewTableFromAboveLists,  
            {NewName, Type.TableColumn(Value.Type(Source), ColumnToShift)}  
        )  
    in  
        ChangedType,  
      
    Source = inputTable,  
    SortedDate = Table.Sort(Source, {"TxnDate", Order.Ascending}),  
    ShiftedDate = fxShiftColumnByOneRow(SortedDate, "TxnDate"),  
    DaysDiff = Table.AddColumn(ShiftedDate, "Days Diff", each  
        if [Next_TxnDate] = null  
            then null  
        else if Duration.Days([Next_TxnDate]-[TxnDate]) <= maxDaysDiff  
            then Duration.Days([Next_TxnDate]-[TxnDate])  
        else  
            null,  
        Int64.Type  
    ),  
    RepeatDate = Table.AddColumn(DaysDiff, "Repeat Date", each  
        if [Days Diff] <> null then [Next_TxnDate] else null,  
        type date  
    ),  
    Reordered = Table.SelectColumns(RepeatDate,  
        Table.ColumnNames(Source) & {"Repeat Date","Days Diff"}  
    )  
in  
    Reordered  

2/ Your main query code:

let  
    Source = Table1,  
    GroupedRows = Table.Group(Source, {"Card#", "Service"},  
        {"CardService", each  
            if Table.RowCount(_) > 1  
            then fxComputeRepeatedService(_, 30)  
            else _,  
            type table  
        }  
    ),  
    CombinedTables = Table.Combine(GroupedRows[CardService])  
in  
    CombinedTables  

Not intensively tested but seems to do what you want. Correspong XL/PQ sample avail. here where I ajdusted your mistake + added a few rows for testing purpose

RE. "...can illustrate a 60 & 180 days as well which I can use for other analysis" => Just change 30 with 60 or 180 in main query code line 6.