cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

Hello everyone,

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

1 ACCEPTED SOLUTION
Community Support

Hi @FDANIEL

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}}),
#"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.

3 REPLIES 3
Community Support

Hi @FDANIEL

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}}),
#"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.

Frequent Visitor

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

Have a nice day,

FDANIEL

Frequent Visitor

Hello,

Thank you very much, your solution works perfectly.

Have a nice day,

FDANIEL

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.