Forum Discussion

Bebs's avatar
Bebs
Helper II
2 years ago
Solved

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

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

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

2 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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