PowerQuery - running difference columns - a bit like running totals

Question

I'm having a articular problem which I'm not sure of the best way to describe.

I am trying to generate a running total and a running difference column in PowerQuery / (the transform part of PowerBI ).

I am looking at population data for 4 different locations in an area by gender. So there is a gender population and a total population for a location. And by extension there is an area gender and area total population.

For example

On a certain date I'm counting the number of people who eat a hamburger. (I'm not, but I'm trying to keep it generic).

On this day there will be a total population for a location.

For example

I want to generate columns for

a running total of : people who have eaten a hamburger at that location (so today's value (tv) added to yesterday's value (yv) ) male who have eaten a hamburger at that location female who have eaten a hamburger at that location

A running difference of: people who have yet to eat a hamburger at that location (so location population yesterday (lpy) - tv) males who have yet to eat a hamburger at that location females who have yet to eat a hamburger at that location

With that defined, it should become easier to build the powerquery up so it's possible to calculate:

the total number of men in a location who have/not eaten a hamburger on a certain date the population of men in that location who have/not eaten a hamburger on a certain date

And so on so that you can calculate how many men in an location have/have not eaten hamburger and how this contributes to the total location proportion and the area proportions of hamburger consumption.

I can quickly generate a quick measure in BI to perform a running total. But the problem I'm having is creating a more complicated running total. And indeed if I'm running this the right way?

I have a table of aggregated data with the population denominator for an area. I want to on a line by line basis in a processed table the remaining population, so that I can say "x % of men, y % of women, xy% of people in location 1 have/have not eaten hamburgers".

I'm not even sure if splitting the table to locations would be right.

Summary: I don't know how to solve this.

A frequently used method to refer to a previous row in PQ is with an Index column. However, that can be slow with large data. A faster method would be to create a new column that is one-off the current column by inserting a null at the beginning, and deleting the last row, of the column of interest. See this for a discussion. — Ron Rosenfeld
Just noticed that, although your question title references Power Query, your question indicates you want to do this in Power Pivot. I can help with the former, but not the latter. Which do you want? — Ron Rosenfeld
Argh. That should read powerquery. I'll change it. I'm doing the work in powerBI but the etl/stuff behind the scenes is done using transform / powerquery. — damo
Thanks Ron. That link gives a further link to a way to do it in DAX using LOOKUPVALUE. That's interesting — damo

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2021-05-21T12:42:39

This may get you started in Power Query.

Here is a method of generating separate running totals for males and females.

It is written as a separate function, so could be used on Grouped tables also.

It uses the List.Generate function, as well as a Buffered List for speed.

fnRunningTotal

(values as list) as list =>
let 
    RT = List.Generate(
            ()=> [RT = values{0}, counter = 0],
            each [counter] < List.Count(values),
            each [RT = [RT] + values{[counter]+1}, counter = [counter]+1],
            each [RT]
    )
in 
    RT

Main MCode edited to shorten the code a bit

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"Location", type text}, {"Gender", type text}, {"Value", Int64.Type}, {"TotalPop", Int64.Type}}),

//add custom columns to separate by Gender
    #"Added Custom" = Table.AddColumn(#"Changed Type", "maleValue", each if [Gender] = "M" then [Value] else 0),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "femaleValue", each if [Gender] = "F" then [Value] else 0),

//running totals for each Gender column
    buffMale = List.Buffer(#"Added Custom1"[maleValue]),
    buffFemale = List.Buffer(#"Added Custom1"[femaleValue]),
    RT = Table.FromColumns(
            List.Combine({Table.ToColumns(#"Added Custom1"),
                          {fnRunningTotal(buffMale), fnRunningTotal(buffFemale)}}),
            List.Combine({Table.ColumnNames(#"Added Custom1"),
                          {"runningTotalMale","runningTotalFemale"}})
        ),
    #"Removed Columns" = Table.RemoveColumns(RT,{"maleValue", "femaleValue"})    
in
    #"Removed Columns"

Original

Results

PowerQuery - running difference columns - a bit like running totals

1 Answers