Convert cumulative data to non cumulative, data is cumulated based on values in another column

[nsabbani]

I am working on a data that has cumulative values in the "value" column and the cumulation is based on the values in "Key Word" column. I need help in creating a new column which gives the the non cumulative values. Adding the picture for reference.

The Key Word column has around 100 unique values repeating multiple times as per the time stamp. And accordingly the cumulative values are recorded in the value column. Need help in converting these cumulative values to non cumulative.

[slorin · Accepted solution]

Hi, @nsabbani 

let
Source = Your_Source,
Group = Table.Group(Source, {"Key Word"}, {{"Data", each 
   [Sort = Table.Sort(_, {{"time", Order.Ascending}}),
    Zip = List.Zip({Sort[value], {0} & List.RemoveLastN(Sort[value],1)}),
    Transform = List.Transform(Zip, each _{0}-_{1}),
    Data = Table.FromColumns(
             Table.ToColumns(Sort) & {Transform},
             Table.ColumnNames(Sort) & {"Non cumulative value"})][Data]}}),
Data = Table.Combine(Group[Data]),
Sort = Table.Sort(Data,{{"time", Order.Ascending}})
in
Sort

    Stéphane

[Anonymous · Accepted solution]

Hi @nsabbani ,

Try steps like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc5BCsAgDETRq5SsBU1MtHWnHkNy/2soLQWRbGbz4DNjQPbJUyC+kAoGcFDrmiigzsDW1uRoY+9r+EQskv4sso1vVm4bvywdyNtbyjbaWd7e4gOqEw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [time = _t, #"Key Word" = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"time", type datetime}, {"Key Word", type text}, {"value", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Key Word", Order.Ascending}, {"time", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Key Word"}, {{"Data", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type)}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {"Data", each Table.AddColumn(_,"valuebefore",(x)=> if x[Index] = 0 then x[value] else x[value]-[value]{x[Index]-1})}),
    #"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"time", "value", "valuebefore"}, {"time", "value", "valuebefore"})
in
    #"Expanded Data"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[slorin · Accepted solution]

Hi, @nsabbani 

let
Source = Your_Source,
Group = Table.Group(Source, {"Key Word"}, {{"Data", each 
   [Sort = Table.Sort(_, {{"time", Order.Ascending}}),
    Zip = List.Zip({Sort[value], {0} & List.RemoveLastN(Sort[value],1)}),
    Transform = List.Transform(Zip, each _{0}-_{1}),
    Data = Table.FromColumns(
             Table.ToColumns(Sort) & {Transform},
             Table.ColumnNames(Sort) & {"Non cumulative value"})][Data]}}),
Data = Table.Combine(Group[Data]),
Sort = Table.Sort(Data,{{"time", Order.Ascending}})
in
Sort

    Stéphane

[Omid_Motamedise]

Why in some rows the values increase in compare to previous data and for some rows its decreased?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...