I am trying to use Power Query to create an accounts receivable (AR) ledger that updates as new revenue and payment detail is added to its respective file. Here is my scenario:
* I have a monthly revenue file (typically one file per month) that has the revenue recorded for each transaction. These are all kept in the same revenue folder.
* I have daily payment files that report the previous day's payments. These are all kept in a separate payments folder.
* The two files (revenue and payment) have a common "Invoice ID" field that I want to do a lookup on to ultimately calculate Revenue - Payments = Accounts Receivable. The Invoice ID for revenue typically only shows up once but it is regularly the case that the same Invoice ID can show up multiple times across payment files (i.e., I can receive multiple payments that need to be applied to one revenue entry). When this is the case, I want to sum up all payments with the same Invoice ID to show one total payment amount to be compared against the respective revenue for that Invoice ID.
* My goal is to link the query (queries) to the two folders so that whenever new data is added to the folder, my AR ledger (most likely in a table or pivot table output in Excel) update via refresh.
* Important to note that both sets of data (especially the daily payment files) include lots of rows of data.
I have tried a few methods and have had some success but here are the problems I've run into:
* I have merged the two queries but when I try to add the revenue and payment data together via Transform > Addition, that option is grayed out for me.
* With only 3 months of payment data (89 daily payment files - ~6MB) and 4 months of revenue data (4 files, total of ~10MB) as my source files, the merged query output file is 12MB and relatively slow. I'm operating from a Microsoft Surface with 16GB of RAM, 15.6 usable currently.
* I have a monthly revenue file (typically one file per month) that has the revenue recorded for each transaction. These are all kept in the same revenue folder.
* I have daily payment files that report the previous day's payments. These are all kept in a separate payments folder.
* The two files (revenue and payment) have a common "Invoice ID" field that I want to do a lookup on to ultimately calculate Revenue - Payments = Accounts Receivable. The Invoice ID for revenue typically only shows up once but it is regularly the case that the same Invoice ID can show up multiple times across payment files (i.e., I can receive multiple payments that need to be applied to one revenue entry). When this is the case, I want to sum up all payments with the same Invoice ID to show one total payment amount to be compared against the respective revenue for that Invoice ID.
* My goal is to link the query (queries) to the two folders so that whenever new data is added to the folder, my AR ledger (most likely in a table or pivot table output in Excel) update via refresh.
* Important to note that both sets of data (especially the daily payment files) include lots of rows of data.
I have tried a few methods and have had some success but here are the problems I've run into:
* I have merged the two queries but when I try to add the revenue and payment data together via Transform > Addition, that option is grayed out for me.
* With only 3 months of payment data (89 daily payment files - ~6MB) and 4 months of revenue data (4 files, total of ~10MB) as my source files, the merged query output file is 12MB and relatively slow. I'm operating from a Microsoft Surface with 16GB of RAM, 15.6 usable currently.
