Group data by consecutive dates per client

Hi Anonymous , 

You could try below M code to see whether it woks or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxU0lEyMNQHIiMDINPQQClWB0nYCCpsiipsChU2BgsnJYGEjfQNzKDChqjCUENMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientID = _t, Transaction_Date = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", type text}, {"Transaction_Date", type date}, {"Amount", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Transaction_Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ClientID"}, {{"ALL", each _, type table [ClientID=text, Transaction_Date=date, Amount=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "GROUP INDEX", each Table.AddIndexColumn([ALL], "INDEX", 0,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ALL"}),
    #"Expanded GROUP INDEX" = Table.ExpandTableColumn(#"Removed Columns", "GROUP INDEX", {"Transaction_Date", "Amount", "INDEX"}, {"Transaction_Date", "Amount", "INDEX"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded GROUP INDEX", "Custom", each try Date.AddDays([Transaction_Date],1)=#"Expanded GROUP INDEX"[Transaction_Date]{[INDEX]+1} otherwise null),
    Custom1 = Table.ReplaceValue(#"Added Custom1", each [Custom], each if [Custom] = false or Value.Is([Custom], type date) then [Transaction_Date] else null , Replacer.ReplaceValue, {"Custom"}),
    #"Filled Up" = Table.FillUp(Custom1,{"Custom"}),
    #"Grouped Rows1" = Table.Group(#"Filled Up", {"ClientID", "Custom"}, {{"sum", each List.Sum([Amount]), type number}, {"all", each _, type table [ClientID=text, Transaction_Date=date, Amount=number, INDEX=number, Custom=date]}})
in
    #"Grouped Rows1"

 You could refer to extract-start-and-end-dates-with-power-query  for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.