Forum Discussion
Cumulative Query Summary - Is it possible?
Hi Anonymous, you can use group function for this task.
Assuming, you have date as a first column, you can remove "invoice number" column, and then group by date and customer, using sum as aggregation function, for each value column.
let
Source = Excel.Workbook(File.Contents("path to excel file"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Invoice Number"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Date", "Customer"}, {{"Invoice Amount", each List.Sum([Invoice Amount]), type number}, {"Current Amount", each List.Sum([Current Amount]), type nullable number}, {"1-14 Days late", each List.Sum([#"1-14 Days late"]), type nullable number}, {"15-29 Days late", each List.Sum([#"15-29 Days late"]), type nullable number}, {"30-59 Days late", each List.Sum([#"30 - 59 Days late"]), type nullable number}, {"60 or more days late", each List.Sum([60 or more days late]), type nullable number}})
in
#"Grouped Rows"
Maybe I should explain that the transactions recived from the query are going to change every day and I want to save a daily summary of those transactions past the point where they would be retrieved from the database.
Essentially a table that is accumulating the history over successive daily refreshes.
Anonymous, ok, altough I dont understand exact steps how you do it, the group function should do the job (if you dont have date column in the first table, you should group only by customers)
Bohumil_Uhrin Yes, I agree Group By would be the way to go with the summary portion. Thank you.