Power Query - Cumulated row, if a certain value is matched

Question

Hey guys,

I got this table in the Power Query Editor. The first column is the date in ascending order. The second one is a part code. The third und fourth columns are the quantity of produced and shipped parts. The last column is the difference between produced und shipped (eg. [Produced]-[Shipped]).

What i need is a new column "Cumulated" like in the example, where the differences are being cumulated between the present "Difference" and the "Cumulated" where the code is the same. So every time in the table when the code is the same, the column "Cumulated" sums the last cumulated value of the code und the present "Difference" value.

This is really tricky in power query, so you would save my day, if you can help me.

Cheers,

Johannes

Answer 1

@M, Johannes

let  
    Source = ...,  
    ChangedType = ...,  
    GroupedRows = Table.Group(ChangedType, {"Code"},  
        {"CODES", each  
            let  
                idx = Table.AddIndexColumn(_, "Idx", 0,1),  
                cumul = Table.AddColumn(idx, "Cumulated",  
                (state)=>  
                    List.Sum(  
                        Table.SelectRows(idx, each state[Idx] >= [Idx])[Difference]  
                    ), Int64.Type  
                ),  
                diffascumul = Table.AddColumn(_, "Cumulated", each [Difference], Int64.Type)  
            in  
                if Table.RowCount(_) = 1 then diffascumul else cumul,  
            type table  
        }  
    ),  
    CombinedCodes = Table.Combine(GroupedRows[CODES]),  
    RemovedIdx = Table.RemoveColumns(CombinedCodes,"Idx")  
in  
    RemovedIdx  

Answer 2

Hi @M, Johannes

let  
    Source = ...,  
    ChangedTypes = ...,  
  
    // Func. by Imke Feldmann - www.TheBIccountant.com  
    fxShiftByOneRow = (MyTable as table, MyColumnName as text) as table =>  
    let  
        Source = MyTable,  
        ShiftedList = {null} &  List.RemoveLastN(Table.Column(Source, MyColumnName),1),  
        Custom1 = Table.ToColumns(Source) & {ShiftedList}  
    in  
        Table.FromColumns(Custom1, Table.ColumnNames(Source) & {"PreviousDiff"}),  
  
    GroupedRows = Table.Group(ChangedTypes, {"Code"},  
        {"CODE", each  
            let  
                shiftedTable = fxShiftByOneRow(_,"Difference"),  
                shiftedCumul = Table.AddColumn(shiftedTable, "Cumulated", each  
                    if [PreviousDiff] is null then [Difference]  
                    else [PreviousDiff] + [Difference],  
                    Int64.Type  
                ),  
                removedPreviousDiff = Table.RemoveColumns(shiftedCumul, "PreviousDiff"),  
                diffAsCumul = Table.AddColumn(_, "Cumulated", each [Difference], Int64.Type)  
            in  
                if Table.RowCount(_) > 1 then removedPreviousDiff else diffAsCumul,  
            type table  
        }  
    ),  
    CombinedNestedTables = Table.Combine(GroupedRows[CODE])  
in  
    CombinedNestedTables  

EDIT: For this case you can replace above lines
1 with:

        ShiftedList = {0} &  List.RemoveLastN(Table.Column(Source, MyColumnName),1),  

and 18-20 with:

                shiftedCumul = Table.AddColumn(shiftedTable, "Cumulated", each  
                    [PreviousDiff] + [Difference],  


  

Answer 3

Hi,
Did you want to add the column in Power Query?
I can get the result with Excel formula after transforming data in power query editor to Excel Worksheet:

=E2+XLOOKUP(B2,$B$1:B1,$E$1:E1,0,0,-1)

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Excel 365 Pro Plus with Power Pivot and Power Query.
Cumulative by code.
Added PivotTable and PivotChart.
http://www.mediafire.com/file/aaafdtexgv8twor/11_30_20.xlsx/file
http://www.mediafire.com/file/f2submfqmwgx0ry/11_30_20.pdf/file

Answer 5

Hi @M, Johannes

Below is another option I tested against a table with 3 different Codes each having 1000 records and perf. are better than the previous option I shared. Each scenario being quite unique you'll have to test it in real conditions. If you do I'll be interested to know how it went on your side

    GroupedRows = Table.Group(ChangedType, {"Code"},  
        {"CODES", each  
            let  
                idx = Table.AddIndexColumn(_, "Idx", 1,1),  
                cumul = Table.AddColumn(idx, "Cumulated", each  
                    List.Sum(  
                        List.Range(idx[Difference],0,[Idx])  
                    ), Int64.Type  
                ),  
                diffascumul = Table.AddColumn(_, "Cumulated", each [Difference], Int64.Type)  
            in  
                if Table.RowCount(_) = 1 then diffascumul else cumul,  
            type table  
        }  
    ),  

And if perf. are also better on your side I will appreciate you mark this option as answer as well => can help others... Thanks