Difference between values in the same column between max and min dates

[tangutoori]

Dear Team,

can you help me to solve the below scenario.

INPUT
Concatenate	Part number	Change Date	Value
30Y0140,30Y01XY,30Y0160,30Y0ABC	30Y0140	05-01-23	10
30Y0140,30Y01XY,30Y0160,30Y0ABC	30Y01XY	06-01-23	10
30Y0140,30Y01XY,30Y0160,30Y0ABC	30Y0160	07-01-23	10
30Y0140,30Y01XY,30Y0160,30Y0ABC	30Y0ABC	08-01-23	20
30Y1534	30Y1534	11-01-23	10
30Y1532	30Y1532	12-01-23	10
30Y0989/30Y0100	30Y0989	13-01-23	23
30Y0989/30Y0100	30Y0100	14-01-23	5
OUTPUT
Concatenate	Part numbers	Dates	Delta
30Y0140,30Y01XY,30Y0160,30Y0ABC	30Y0140 & 30YABC	05-01-2023 & 08-01-23	10
30Y1534	30Y1534	11-01-23	10
30Y1532	30Y1532	12-01-23	10
30Y0989/30Y0100	30Y0989 & 30Y0100	13-01-2023 & 14-01-2023	18

Regards,

narender.

[Anonymous · Accepted solution]

Hi @tangutoori ,

You can achieve it in Power Query Editor, please find the details in the attachment.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY5LDoAgDAXvwpqEfgBxqV5CYrj/NSxUcWOMYdWhzMvrcRiGDOjBtrlnnVHfy7oZextCwaEjIBZEMMX+Tu9ZKI6mY+2eBtNK6UlTT2Ngr9ZFiG8l8kldq4T0esucZtduALi6ZVMVh9zL+UNXEt3fejClnA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Concatenate = _t, #"Part number" = _t, #"Change Date" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Concatenate", type text}, {"Part number", type text}, {"Change Date", type date}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Concatenate"}, {{"detail", each _, type table [Concatenate=nullable text, Part number=nullable text, Change Date=nullable text, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Part numbers", each let 
min = List.Min([detail][Change Date]),
max = List.Max([detail][Change Date]),
result = 
if min=max 
then min
else Text.From(min) & " & " & Text.From(max)
in
result),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Dates", each let 
min = List.Min([detail][Change Date]),
max = List.Max([detail][Change Date]),
minpart = Table.SelectRows([detail],each [Change Date] = min)[Part number]{0},
maxpart = Table.SelectRows([detail],each [Change Date] = max)[Part number]{0},
result = 
if 
min = max
then minpart
else 
minpart & " & " & maxpart
in 
result),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Delta", each let 
min = List.Min([detail][Change Date]),
max = List.Max([detail][Change Date]),
mindvalue = Table.SelectRows([detail],each [Change Date] = min)[Value]{0},
maxdvalue = Table.SelectRows([detail],each [Change Date] = max)[Value]{0},
result = 
if 
mindvalue = maxdvalue
then mindvalue
else 
Number.Abs(maxdvalue-mindvalue)
in 
result),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"detail"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Part numbers", type text}, {"Dates", type text}, {"Delta", Int64.Type}})
in
    #"Changed Type1"

Best Regards

[lbendlin]

This is not something you should attempt to do in Power BI.