Running Totals in Power Query (Get and Transform)

[TDC21]

Our inventory table calculates a current balance and our inventory transaction table shows all transactions by date but no balance. I have rearranged the transaction table to provide a period end date and activity summed by period. I have also included an date Index column and created duplicates of the Index and period columns to self reference. What I am trying to get now is a balance by period but I cant find a way to make this work yet. My query includes the following columns,


[Index] [Index_B] [END_DATE] [END_DATE_B] [PART] [PERIOD ACTIVITY]


I am looking for a way to sum [PERIOD ACTIVITY] by [PART] where [END_DATE] <= [END_DATE_B]

or

sum [PERIOD ACTIVITY] by [PART] where [Index] <= [Index_B]


Any help is greatly appreciated

 

[SyedUsman]

Please Post sample Data for better understanding of your question

Cheers

 

[tallan]

Have you considered importing the transaction table into Power Pivot, adding a date table, which is super easy with Excel 2016, and (after creating a relationship between the date and transaction table) letting the pivot table list your balances?

Tom
PowerPivotPro - Transforming your Business with Power Pivot and Power BI

 

[TDC21]

this is a pretty simple version of what I am working with, it is transactions by date and part. The date has been converted to EOMONTH and I also added a index by date column. I made the duplicate columns thinking some kind of self reference might work. I have had issues finding the correct function or query stmt but in a simple way of saying it I am trying to

SUM [PERIOD ACTIVITY] BY [PART] WERE [END_DATE] <= [END_DATE_B] Just a running balance based on transactions by part and date


here is a simple example of my data set,

Transaction	Index	Index_B	PART	END_DATE	END_DATE_B	PERIOD ACTIVITY
1	1	1	05000X135SA51670	1/31/2017	1/31/2017	20
2	1	1	06250SA240304	1/31/2017	1/31/2017	10
3	1	1	05000X135SA51670	1/31/2017	1/31/2017	-5
4	2	2	06250SA240304	2/28/2017	2/28/2017	2
5	2	2	05000X135SA51670	2/28/2017	2/28/2017	-10
6	3	3	06250SA240304	3/31/2017	3/31/2017	-8
7	3	3	05000X135SA51670	3/31/2017	3/31/2017	10
8	4	4	06250SA240304	4/30/2017	4/30/2017	5
		DESIRED RESULTS
			PART	END_DATE	PERIOD ACTIVITY
			05000X135SA51670	1/31/2017	15
			06250SA240304	1/31/2017	10
			05000X135SA51670	2/28/2017	5
			06250SA240304	2/28/2017	12
			05000X135SA51670	3/31/2017	15
			06250SA240304	3/31/2017	4
			05000X135SA51670	4/30/2017	15
			06250SA240304	4/30/2017	9

<colgroup><col><col span="2"><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[TDC21]

I have not tried power pivot with a date table, ideally i am hoping to find a way to do this as a query so the results themselves can then be linked back to several other tables.

 

[tallan]

Dropbox Link

The dropbox link will connect you to an example of a running total in Power Query using your data set to start and then finishing with the desired results.

The solution required some advanced steps such as custom M code for merging and appending data sets created in the query, a Cartesian join of dates and parts to handle cases where there is no part activity in a given month (see April in source and April in desired results), and a custom function to handle the actual running total.

Most of the work was in preparing the data. Once the basic table is prepared using a group by on part and date (summing the Activity Total), the function receives parameters from each row for part, date and a copy of the prepared table. Inside the function, the table copy is filtered for required dates and part and then a "group by" is used to sum the period activity column for the running total.

Tom
PowerPivotPro - Transforming your Business with Power Pivot and Power BI

 

[TDC21]

Thank you Tallan,

I am fairly new to M-Code so Im sure it will take me some time to get my head wrapped around all of that but it is an excellent example so I will defiantly spend some time on it.

Do you know if the code will operate on large data sets efficiently? Roughly 400,000 total lines and 80,000 after the initial grouping (part & period). If data size is an issue I could run the first layer or two of groupings as a custom view in SQL to limit the data source file size for excel. Not sure if that would be beneficial or not.

 

[tallan]

Hi TDC21,

This approach will not be efficient for datasets that are still relatively large after the first grouping. For an efficient solution with large data sets, I recommend grouping data in Power Query on balance date and part, then importing into the Excel Data Model (the Power Pivot window), followed by creating a measure to handle the running total, and then creating a pivot table with the Display option set for "Show items with no data on rows".

Tom