CODEWITHSUNDEEP
powerbipowerquerypowerbi-desktop
AzUser1
AzUser1

Reputation: 193

Power BI: Create a relative column in power query

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!

Upvotes: 1

Views: 139

Answers (1)

davidebacci
davidebacci

Reputation: 30289

enter image description here

enter image description here

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]

Upvotes: 1

Related Questions