Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

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:

 

 

CustomerInvoice NumberInvoice AmountCurrent Amount1-14 Days late15-29 Days late30 - 59 Days late60 or more days late
Customer A123500500    
Customer A124500 500   
Customer A125500  500  
Customer A126500   500 
Customer A127500    500
Customer B128500500    
Customer B129500500    
Customer B130500 500   
Customer B131500   500 
Customer B132500   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 DateCustomerNameSum of Invoice AmountSum of Current AmountSum of 1-14 Days lateSum of 15-29 Days lateSum of 30 - 59 Days lateSum of 60 or more days late
9/28/2020Customer A2500500500500500500
9/28/2020Customer B25001000500 1000 
9/29/2020Customer A2500500500500500500
9/29/2020Customer B25001000500 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 8
Bohumil_Uhrin
Helper II
Helper II

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"

 

Anonymous
Not applicable

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)

Anonymous
Not applicable

@Bohumil_Uhrin  Yes, I agree Group By would be the way to go with the summary portion. Thank you.

Icey
Community Support
Community Support

Hi @Anonymous ,

 

Is this problem solved?

 

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If not, please let me know.

 

 

Best Regards,

Icey

Anonymous
Not applicable

@Icey  Not solved yet. The real question is how to store cumulative summary of past queries in the data model somehow. I will try some ideas to do this.

AlB
Super User
Super User

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 

 

SU18_powerbi_badge

Anonymous
Not applicable

@AlB 

 

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.