Total less available = Remaining Formula in Power Query

Question

I have a bunch of statistical data that I have chopped up and pieced together in power query. In looks like there is category missing from the Database. To fill in the gaps I am trying to take the grand totals which are correct (red), subtract from that what I know to be correct, with the remaining numbers giving me my answer (orange).

The correct data starts with I1, I2, I3, I4, so possibly a grand total of these, by state.

At the moment this is filled by the following formula in excel;

=E53-SUMIFS($E$5:$E$44,$B$5:$B$44,B45,$C$5:$C$44,C45,$D$5:$D$44,D45)

Any help with how the heck I can do this in power query. I realise I cant use the same formula but any ideas would be much appreciated. I can change the text in red to total if that helps in some way?

Thanks

Answer 1

Here's one potential way. If you start with your spreadsheet set up similar to this:

I only used a subset of your StateIDs from your example and generated my own Values for this example. And the figures in the Available column would be from your red section.

Then add the table from the spreadsheet to Power Query (in Excel, you would click on the table and then Data > From Table/Range > Select My table has headers and click OK).

In Power Query:

You'll probably have to change the TimeID type to date if you want to use the dates for anything, because it will probably come in as date-time type--I won't use the dates here though, so you could skip changing the type (otherwise, right-click the TimeID column > Change Type > Date)

Then use Group By to aggregate values and set the stage for the calculation you want (select the StateID > Group By > and setup the groupings like below and click OK)

You should see something like this:

Then add a new column with your calculation (Add Column > Custom Column > Set it up like below and click OK)

You should see something like this: