•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
A way to join tables or keep separate?
Our take
Managing budgets effectively is crucial for services companies, especially when clients request detailed fiscal year summaries. In this scenario, the challenge arises from separating projected billings and invoiced amounts into distinct tables within the data model. While leveraging Power Query can streamline your data organization, it can also complicate the process. The goal is to create a unified output that displays both invoiced and projected billings, grouped by fiscal year. Let’s explore how to achieve this integration efficiently, enhancing your reporting capabilities.
Hello, folks. I run a services company. Often my clients ask for a budget summary broken down by fiscal year (including PROJECTED billings). Last year I got smart and added a formula to calculate the fiscal year (e.g. 2025-25 for those starting in January, and 2025-26 for those with a July FY start date) for each billing milestone ([Milestones table]. That enabled me to produce the customer summary table (really a GROUPBY array) in the screenshot.
COMPLICATION
I decided to streamline [Milestones] by moving the invoice detail to another table. Now I have a situation where the PROJECTED billings and their [expected invoice date] are in one table, and the INVOICED billings are in another table with [paid date]. These tables are in the data model (see attached) so i tried to get a pivot table using the linked tables to work, but it is not working as expected. (See data model screenshot.)
QUESTION
Is there a way to output both invoiced and projected billings in one table, grouped by the (customer-specific) FY? In my mind I can create a joined table in the data model, but I am no db expert and I am still struggling with powerquery.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Creating Pivot Table from Multiple SheetsHi All, I'm working on a large tracking workbook, consisting of several clinical trials in order to track by patient detail the payments we are owed by the funder, what we have received, and the difference. All these payments are delayed by 3m-2 years in some cases and leadership wants to accurately predict how much we are owed. I think what where I'm running into issues is that while I did standardized as much as I could, there are still several columns for each study that don't apply to other studies. I.e. some studies have different arms they could be enrolled in, some are just a 1 time enrollment payment, others have several milestones that can receive payments. But every sheet has roll ups that are standardized that I need in the Pivot Table. Those being: Protocol Randomized Date Federal Accrued Foundation Accrued Industry Accrued Supplement Accrued Federal Received Foundation Received Industry Received Supplement Received Total Owed The Accrued and received columns sum the individual payments into those buckets, that way we can go back to the funder and ask specifically what we are missing for to see if they missed paying us for that milestone specifically. When I tried pulling all these sheets into Power Query, I was able too, and aggregated all the sheets into one via Power Query. Then I tried to pull that aggregate into a pivot table. No Pivot table Loaded and all I got was "load to data model failed" on each queries. Am I asking for too much? Can I get rid of the extra columns in Power Query that do no align together with ruining the data that is being pulled in by formulas. I have if statements pulling into the table for the individual, study specific milestones, from a separate table that automatically helps us track payments accrued, and the "standard columns" have sums formulas that sum the columns that apply to them from the individual milestone columns. The milestone, study specific received columns are entered in manually and have no formulas, but are rolled up into the standard columns just like in the accrued side. And the total owed column is also a formula of the standard accrued and received columns. The goal of pulling this into a pivot table is so we can give high level data to leadership to actually start tracking how much we are owed, given the constant delay in payments, and to have a real sense of the deficit this specific program runs year to year. This way they can accurately plan for the yearly "donation" from other sources of funding in the department. If you made it through this post, thank you! Any help is appreciated. I'm using Excel 365. submitted by /u/Melodic-Pollution-91 [link] [comments]
- How do I Maximize File EfficiencyI work with data sets that I typically look at forecast by year. Currently when I look at 2026 and 2027 it is rougly 1.4M lines of data. I have to put these in two separate data pulls and tables. Then I have six different customers included in this data. so I have to create 6 tabs with six diffrent pivot tables for them to look at. This has created a massive file that lags just to open, save or close so I really have two questions and am open to suggestions. Would it be better to store the data in one worksheet and then link a second worksheet that just has the pivot tables and separated look? If so how would I creat that link? Can you explain to me like I am 5 how I would use power query to combine the 26 and 27 table so that they could be in the same pivot table? Every column in both are identical. submitted by /u/dcal69 [link] [comments]
- Power Query Start Year to Year convertHello Everyone. A monthly report is generated as shown on the left. I am hoping to use Power Query to adjust the columns as shown on the right so the total cost per year is easier to visualize. Is there a simple way to use PQ for this? It isn't too hard to manually filter and do this but I'd like to remove any manual steps if possible. Data Desired Output Project Name Project Start Year Year 1 Year 2 Year 3 Year 4 Project Name Project Start Year 2024 2025 2026 2027 2028 2029 Alpha 2024 1,000.00 600.00 200.00 0.00 Alpha 2024 1,000.00 600.00 200.00 0.00 0.00 0.00 Bravo 2025 200.00 0.00 0.00 0.00 Bravo 2025 0.00 200.00 0.00 0.00 0.00 0.00 Charlie 2024 500.00 500.00 500.00 100.00 Charlie 2024 500.00 500.00 500.00 100.00 0.00 0.00 Delta 2026 200.00 100.00 0.00 0.00 Delta 2026 0.00 0.00 200.00 100.00 0.00 0.00 Echo 2026 1,500.00 1,000.00 500.00 250.00 Echo 2026 0.00 0.00 1,500.00 1,000.00 500.00 250.00 Foxtrot 2025 1,000.00 1,000.00 1,000.00 1,000.00 Foxtrot 2025 0.00 1,000.00 1,000.00 1,000.00 1,000.00 0.00 submitted by /u/XEP19 [link] [comments]
- Separating Monthly Results in Data for Same Item?Hey all, I’ve got a dataset that I’m having some trouble separating for calculating properly on a pivot table. It is a list of items in Column A, which all belong to a group in Column B, and then C D and E are results for Q1 so Jan, Feb, and March. the problem is that we have a budget figure, a forecast figure, two new forecast figures and the actual figure for each item, that are stacked in the column. so for the first item there are five rows of data that I would like to compare for each month. I just can’t figure out how to do this in a pivot table because the forecast/new forecast/budget/actuals aren’t column headers. I tried adding new columns that totaled up the rows for each category of forecast/actual etc but the results were far more than the real actuals so something failed there. Thanks! submitted by /u/Woodit [link] [comments]