Forum Discussion
(Power-Query) Finding min in a column, subtracting it from other values and grouping the difference
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?
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 addYearCatExample output:
Pete
1 Reply
- BA_Pete
Super User
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 addYearCatExample output:
Pete