calculate delta for group

[Hansolu]

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

[Breezhall]

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

[v-aatheeque]

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]

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.

[ronrsnfld]

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:

[tharunkumarRTK · Accepted solution]

@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!