lets say I have the following table:
| Year | Patch | Value |
|---|---|---|
| 2021 | 1.68 | 23.5 |
| 2021 | 1.70 | 25.5 |
| 2022 | 1.75 | 21.5 |
| 2022 | 1.79 | 24.5 |
| 2023 | 1.84 | 25.5 |
| 2023 | 1.89 | 28.5 |
I want to create a column with a boolean value "new" and "old" in power query based on the year and the patch version. the smaller patch version in that year should be the old version and higher patch version the new one.
Like getting a result like this:
| Year | Patch | Value | Patch Type |
|---|---|---|---|
| 2021 | 1.68 | 23.5 | old |
| 2021 | 1.70 | 25.5 | new |
| 2022 | 1.75 | 21.5 | old |
| 2022 | 1.79 | 24.5 | new |
| 2023 | 1.84 | 25.5 | old |
| 2023 | 1.89 | 28.5 | new |
How can I do this in power query?
Thanks!
>Solution :
Add a new column with the following code.
[t=
Table.SelectRows(#"Changed Type", (x)=> x[Year] = [Year]),
min = List.Min(t[Patch]),
max = List.Max(t[Patch]),
result = if [Patch] = min then "old" else if [Patch] = max then "new" else null
][result]