calculate delta for group

Hansolu 

Hope this matches your expected output

Please find the M code

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
  #"Removed columns" = Table.RemoveColumns(Source, {"Index"}),
  #"Changed column type" = Table.TransformColumnTypes(#"Removed columns", {{"Group", Int64.Type}, {"Material", type text}, {"currency_type", type text}, {"cost_total", type number}}),
  #"Pivoted column" = Table.Pivot(Table.TransformColumnTypes(#"Changed column type", {{"currency_type", type text}}), List.Distinct(Table.TransformColumnTypes(#"Changed column type", {{"currency_type", type text}})[currency_type]), "currency_type", "cost_total"),
  #"Added custom" = Table.TransformColumnTypes(Table.AddColumn(#"Pivoted column", "Next Group", each [Group] + 1), {{"Next Group", Int64.Type}}),
  #"Merged queries" = Table.NestedJoin(#"Added custom", {"Next Group"}, #"Added custom", {"Group"}, "Added custom", JoinKind.LeftOuter),
  #"Expanded Added custom" = Table.ExpandTableColumn(#"Merged queries", "Added custom", {"Local", "group_currency"}, {"Next Group.Local", "Next Group.group_currency"}),
  #"Unpivoted columns" = Table.UnpivotOtherColumns(#"Expanded Added custom", {"Group", "Material", "Next Group", "Next Group.Local", "Next Group.group_currency"}, "currency_type", "cost_total"),
  #"Added custom 1" = Table.TransformColumnTypes(Table.AddColumn(#"Unpivoted columns", "Delta last cost", each if [currency_type] = "Local" then  [cost_total] - [Next Group.Local] else  [cost_total] - [Next Group.group_currency]), {{"Delta last cost", type number}}),
  #"Removed columns 1" = Table.RemoveColumns(#"Added custom 1", {"Next Group", "Next Group.Local", "Next Group.group_currency"})
in
    #"Removed columns 1"

 File link: Delta table group.xlsx

Connect on LinkedIn

Blogs: https://www.techietips.co.in/

Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!