Forum Discussion

Hansolu's avatar
Hansolu
Helper II
7 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 material, local and group currency and the cost.

 

I want to add a column to show the delta from Group 1 to Group 2 for the group_currency and for the local_currency.

 

In PQ i can filter, then my formula works. I want to modify the formula but cant find the proper formula to make it work.

Idea is: Add a sequence, modify the sequence to only have a number for group_currency, find the position in the list of sequence, add one position, and then calculate the difference between current row and the position found.

 

Any ideas what i do wrong? 

example file 

thanks

 

Need Help
  • tharunkumarRTK's avatar
    tharunkumarRTK
    7 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!

5 Replies

  • tharunkumarRTK's avatar
    tharunkumarRTK
    Super User
    7 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!
  • ronrsnfld's avatar
    ronrsnfld
    Super User
    7 months ago

    Please read the code comments for explanation of the steps. 

    let

//change next line to reflect actual data source
//don't need an Index column in the source data
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Group", Int64.Type}, {"Material", type text}, {"currency_type", type text}, 
        {"Index", Int64.Type}, {"cost_total", type number}}),
    
//Group by Group and Material
//Extract the Local and group_currency values into separate columns
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group", "Material"}, {
        {"Currency_type", each Record.FromList([cost_total],[currency_type]),
                    type[Local=Currency.Type, group_currency=Currency.Type]}}),
    #"Expanded Currency_type" = Table.ExpandRecordColumn(#"Grouped Rows", "Currency_type", {"Local", "group_currency"}),

//Add Index column so we can compute the delta betweeen each group of the group_currency
    #"Added Index" = Table.AddIndexColumn(#"Expanded Currency_type", "Index", 0, 1, Int64.Type),
    #"Added Delta" = Table.AddColumn(#"Added Index","Delta", each 
        try ([group_currency] - #"Added Index"{[Index]+1}[group_currency]) otherwise null, Currency.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Delta",{"Index"})
in
    #"Removed Columns"

    Source Data:

    Results:

     

     

     

  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    7 months ago

    Hi Hansolu 

    Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.

    • v-aatheeque's avatar
      v-aatheeque
      Community Support
      7 months ago

      Hi Hansolu 


      Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

  • 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