in PowerQuery can we do calculation and store it in a variable ?

[Bebs]

Hello,

In Power Query, I've a huge table.

I'd like to get the max year(field) with a filter (REGION='US')

is it possible to make this calculation, rhen put it in a variable (or something like it)

then be able to use  this variable in a filter of another table (= Table.SelectRows(Source, each ([YEAR] < maxyear)) ?

(I need to avoid join tables)

Best regards

[PhilipTreacy · Accepted solution]

Hi @Bebs 

You can create a new query and write code to get the max year and then use that in your Table.SelectRows, or you can reference the max year directly like this

= Table.SelectRows(#"Previous Step Name", each [Year] < List.Max(Table.SelectRows(TableA, each ([Region] = "US"))[Year]))

Where TableA is your table with the years and regions.

Please download this PBIX file to see examples of both methods 

Regards

Phil

[v-zhouwen-msft · Accepted solution]

Hi @PhilipTreacy ,thanks for the quick reply.You are in the right direction. I'll add further.

Hi @Bebs ,

Please refer to the steps provided by PhilipTreacy .

The Table data is shown below:

Please follow these steps:

1.Create a Blank Query

2.Use the following code(Replace your own file paths)

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-zhouwenbin\Desktop\(1)2024.4.19.xlsx"), null, true),
    Table1_Sheet = Source{[Item="Table1",Kind="Sheet"]}[Data],
    Table2_Sheet = Source{[Item="Table2",Kind="Sheet"]}[Data],
    a = Table.PromoteHeaders(Table1_Sheet, [PromoteAllScalars=true]),
    b = Table.PromoteHeaders(Table2_Sheet, [PromoteAllScalars=true]),
    FilteredRows = Table.SelectRows(a, each [REGION] = "US"),
    maxyear = List.Max(FilteredRows[Year]),
    c = Table.SelectRows(b,each [Year] < maxyear)

    

    
in
    c

3.Final output

[PhilipTreacy · Accepted solution]

Hi @Bebs 

You can create a new query and write code to get the max year and then use that in your Table.SelectRows, or you can reference the max year directly like this

= Table.SelectRows(#"Previous Step Name", each [Year] < List.Max(Table.SelectRows(TableA, each ([Region] = "US"))[Year]))

Where TableA is your table with the years and regions.

Please download this PBIX file to see examples of both methods 

Regards

Phil