Forum Discussion
Cumulative Query Summary - Is it possible?
Good Morning Power BI Community,
I have a business need that I am looking to fill and was wondering if this is possible in Power BI & Power Query.
Basically I have a query that is run on a daily basis via an odbc connection to Power Query that contains open invoice detail for that day listed by invoice number and customer similar to the table below:
| Customer | Invoice Number | Invoice Amount | Current Amount | 1-14 Days late | 15-29 Days late | 30 - 59 Days late | 60 or more days late |
| Customer A | 123 | 500 | 500 | ||||
| Customer A | 124 | 500 | 500 | ||||
| Customer A | 125 | 500 | 500 | ||||
| Customer A | 126 | 500 | 500 | ||||
| Customer A | 127 | 500 | 500 | ||||
| Customer B | 128 | 500 | 500 | ||||
| Customer B | 129 | 500 | 500 | ||||
| Customer B | 130 | 500 | 500 | ||||
| Customer B | 131 | 500 | 500 | ||||
| Customer B | 132 | 500 | 500 |
The business need is to summarize each days query by customer number, date stamp each summary, and store that summary in a new table so that the historical data for day is preserved for comparision and tracking purposes, something like the table below.
| Reporting Date | CustomerName | Sum of Invoice Amount | Sum of Current Amount | Sum of 1-14 Days late | Sum of 15-29 Days late | Sum of 30 - 59 Days late | Sum of 60 or more days late |
| 9/28/2020 | Customer A | 2500 | 500 | 500 | 500 | 500 | 500 |
| 9/28/2020 | Customer B | 2500 | 1000 | 500 | 1000 | ||
| 9/29/2020 | Customer A | 2500 | 500 | 500 | 500 | 500 | 500 |
| 9/29/2020 | Customer B | 2500 | 1000 | 500 | 1000 |
I am trying to guage if this is possible and how technically difficult this would be. I have searched the web and was unable to find solutions exactly like this. I did however find the List.Accumulate function for M lists, would that be the way to go?
Any guidance would be appreciated and if possible point me in the direction of a solution.
Thanks,
Aaron
8 Replies
Hi Anonymous
This is relatively simple in PQ but... where is the date on the first table?? Can you post the full table including the data that needs to be processed?
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
The date in the second table is generated by power query and would represent the date the query was refreshed and the summary was posted to the cumulative table. I can get an invoice date in the first table though. Does that help?
Thanks,
Aaron
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)
