Power Query : Make a Cumulative sum if the value of the previous line is the same

[Anonymous]

Hello everyone,

I need your help with a question about Power Query.

Let me explain:

I need to do a conditional cumulative sum:

In a column I have a string of numbers and letters that we call "Item Key" and a column of "Quantity". If this key is identical on the previous line then you have to add the Quantity that is in a column of numbers with the result found in the previous line. But if they are different we don't add anything and we only copy the "Quantity".

I put you the Excel equivalent of what I want in M (the formula is written in cell AA2): =SI([@[Clé Art_Site]]=W19;[@[Qté besoin hors CTRM]]+AA19;[@[Qté besoin hors CTRM]])

I specify that I must absolutely do it in M I can not do it in DAX.

I hope you will understand my request and I thank you in advance for your help,

Sincerely,

FDANIEL

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Thanks for reaching out to us.

>>I want in M (the formula is written in cell AA2): =SI([@[Clé Art_Site]]=W19;[@[Qté besoin hors CTRM]]+AA19;[@[Qté besoin hors CTRM]])  ....I specify that I must absolutely do it in M I can not do it in DAX.

You can try this, copy it and put into Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc5BDsAgCATAv3g2QVDb+hbjof//RIU1hcNyYLKEOZMUYRrEKadXB7LyES4g2QNxYqe60wMJSNfNcmjvIc0KoSQkLpc+wU4VpIXb8ktzebQU7nWQ3hqWtT4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Item Key" = _t, Quantity = _t, #"Expected result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Item Key", type text}, {"Quantity", Int64.Type}, {"Expected result", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index1",{{"Index", "I1"}, {"Index.1", "I2"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"I1"}, #"Renamed Columns", {"I2"}, "Renamed Columns", JoinKind.LeftOuter),
    #"Expanded Renamed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Renamed Columns", {"Item Key", "Quantity"}, {"Renamed Columns.Item Key", "Renamed Columns.Quantity"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Renamed Columns", "Custom", each if [Item Key]=[Renamed Columns.Item Key] then [Quantity]+[Renamed Columns.Quantity] else [Quantity]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"I1", "I2", "Renamed Columns.Item Key", "Renamed Columns.Quantity"})
in
    #"Removed Columns"

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello,

I come back to you because in my data I sometimes have the key item that comes back 4 times in a row therefore the accumulation must be done on the 4 except that your code accumulates only the last 2.

Here is an example of my data that I need to accumulate. My total data is composed of about 20 columns and +100 000 rows

ItemKey	Quantity	Résult
000040_12D18	1898,4	1898,4
000073_12D18	292,5	292,5
000079_12D18	50	50
000079_12D18	25	75
000152_12D00	150	150
000152_12D00	258	408
000152_12D00	15	423

Thank you in advance for your help.
Have a nice day,

FDANIEL

[Anonymous]

Hello,

Thank you very much, your solution works perfectly.

Have a nice day,

FDANIEL