Forum Discussion

Hansolu's avatar
Hansolu
Icon for Helper II rankHelper II
8 months ago
Solved

calculate delta for group

Hi   i do have a list of different materials. Each material is showing the cost in group and local currency. Each Material is updated periodically (new group). e.g.  Group 1 have 2 rows for each m...
Need Help
  • tharunkumarRTK's avatar
    tharunkumarRTK
    8 months ago

    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!
Breezhall's avatar
Breezhall
Frequent Visitor
7 months ago

 

 

let
    source = Excel.CurrentWorkbook(){[Name="t1"]}[Content],
    sort = Table.Sort(source,{{"Group", Order.Ascending}, {"currency_type", Order.Ascending}}),
    lst = Table.Split( sort,2),
    num = {0.. List.Count( lst)-1},
    transform = List.Transform( num,(x)=> 
                                        [a=try lst{x}[cost_total]{0}-lst{x+1}[cost_total]{0} otherwise null,
                                        b=try lst{x}[cost_total]{1}-lst{x+1}[cost_total]{1} otherwise null,
                                        c= {a,b}  ][c] ),
    combine = Table.ToColumns(sort)&{List.Combine( transform)},
    table = Table.FromColumns( combine,Table.ColumnNames(source)&{"Delta last cost"}),
    result = Table.Sort(table,{{"Index", Order.Ascending}})
in
    result