(Power-Query) Finding min in a column, subtracting it from other values and grouping the difference

[Karthik50]

Hi,

In Power-Bi's Power-Query editor, I have a year column.

Year

2000

2001

2002

2003

2004

I want to find the minimum of the column (in this case, the year "2000"), subtract this value from each row in the column (in this case, the values are 0,1,2,3,4) and categorize the values into bins (in this case, ❤️ are 1, >=3 and <6 is 2). So:

Year	Difference from Minimum Year
2000	0
2001	1
2002	2
2003	3
2004	4

Year	Difference from Minimum Year	Category
2000	0	1
2001	1	1
2002	2	1
2003	3	2
2004	4	2

I tried List.Min but am not able to move ahead. Can you pleae help me how I can do this? 

[BA_Pete · Accepted solution]

Hi @Karthik50 ,

The trick to getting the minimum year value is to reference the previous step in your min function. The categorisation of the years is just a simple 'if' statement:

Example code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMFCK1QEzDGEMIxjDGMYwUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
    
// Relevant steps from here =======>
    addDiffFromMin = Table.AddColumn(chgTypes, "DiffFromMinYear", each [Year] - List.Min(chgTypes[Year])),
    addYearCat = Table.AddColumn(
        addDiffFromMin,
        "YearCategory",
        each if [DiffFromMinYear] < 3 then 1
        else if [DiffFromMinYear] < 6 then 2
        else 999
    )
in
    addYearCat

Example output:

Pete