CODEWITHSUNDEEP
powerbipowerquerypowerbi-desktopm
AzUser1
AzUser1

Reputation: 193

Power BI: Create a relative column in power query based on the highest value

lets say I have the following table:

Year Patch Value
2021 1.65 23.5
2021 1.67 21.3
2021 1.70 25.5
2022 1.72 23.2
2022 1.75 21.5
2022 1.79 24.5
2023 1.81 23
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. There are many patch versions in a year. The highest one from the year is always "new". But the patch before should be "old".

Like getting a result like this:

Year Patch Value Patch Type
2021 1.67 21.3 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

Is there a way in power query to get the nearest value based on the latest patch?

Thanks!

Upvotes: 1

Views: 366

Answers (2)

davidebacci
davidebacci

Reputation: 30289

Here you go.

enter image description here

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Patch", type number}, {"Value", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Year"}, {{"All", each _, type table [Year=nullable number, Patch=nullable number, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", (x)=> 
    [ 
    a = Table.FirstN( Table.Sort(x[All],{ {"Patch", Order.Descending}} ),2),
    b = Record.AddField(a{0}, "Patch Type", "new"),
    c = Record.AddField(a{1}, "Patch Type", "old"),
    d = Table.FromRecords({b,c})
    ][d]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Year", "All"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Year", "Patch", "Value", "Patch Type"}, {"Year", "Patch", "Value", "Patch Type"})
in
    #"Expanded Custom"

Upvotes: 2

Abishek VK
Abishek VK

Reputation: 524

You can create a new measure to calculate the max for each year and use that measure to create a new column, below are the formulas. To find the max,

MaxPatch = CALCULATE (
    MAX ( [Patch]),
    ALLEXCEPT ( test,test[Year] )
)

To calculate the patch type,

Patch_type = IF(test[Patch]=[MaxPatch],"New","Old")

Please try this.

Upvotes: 0

Related Questions