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

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