Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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.
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
@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.
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
12 | |
11 | |
11 | |
8 |
User | Count |
---|---|
43 | |
25 | |
16 | |
14 | |
12 |