CODEWITHSUNDEEP
powerquery
Andy
Andy

Reputation: 3

Power Query Create Max Variable Based on Two Columns

I'm very new to Power Query, and am trying to create a variable to return the maximum week value in the maximum year. In the example below I want to return 42.

Year Week
2023 42
2023 41
2022 52
2022 51

Appreciate this may not be the best way to solve my problem, so below is what I am trying to achieve in full. I have ~500k rows of sales data, by week and year. I want to have a filter in various reports for last 52 weeks. My thought was to create a column "Last 52 weeks" that returns TRUE/FALSE, based on a variable

= Table.AddColumn(#"Changed Type", "Custom", each if ( [Year]=List.Max(#"Changed Type"[Year]) ) or ( [Year]=List.Max(#"Changed Type"[Year]) -1) and [Week] > 41 then 
"TRUE"
else 
"FALSE")

Where 41 is the maximum week. This is the part I can't figure out.

Any help greatly appreciated, either on how to find the maximum week and use that in my calculation, or a better way of solving this!

Many thanks Andy

Upvotes: 0

Views: 425

Answers (1)

horseyride
horseyride

Reputation: 21373

this pulls the max week from the max year

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
MaxYearWeek = List.Max(Table.SelectRows(Source, each [Year] =List.Max(Source[Year]))[Week])
in MaxYearWeek

List.Max(Source[Year]) is the Max year. You select all the rows that have that with Table.SelectRow. From that table, you choose the [Week] column and do List.Max on that

Upvotes: 0

Related Questions