Forum Discussion
Convert cumulative data to non cumulative, data is cumulated based on values in another column
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.
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
SortStéphane
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 TeamIf 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
4 Replies
- lbendlin
Super User
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-Forum/ba-p/963216
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/1447523 - Omid_Motamedise
Why in some rows the values increase in compare to previous data and for some rows its decreased?
slorinHi, 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
SortStéphane
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 TeamIf 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
