Forum Discussion

edhans's avatar
edhans
Community Champion
6 years ago
Solved

Reference a column to filter a nested table in Power Query?

I am trying to filter a column in a nested table by a column in the query that holds the nested table.  In the [All Rows] column are nested tables that have the year and main account number and amou...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    6 years ago

    edhans 

     

    If i understand the problem correctly

     

    each let myyear=[Year]
in
Table.SelectRows([All Rows], each [Year] >= myyear))
edhans's avatar
edhans
Community Champion
6 years ago

EDIT: Nevermind. Got it! I see now I can have additional commas between the each and let without messing up the Table.AddColumn() function.

 

= Table.AddColumn(Source, "Custom",
each let varIndex = [Index], varCategory = [Category], VarMonth = [Month Number]
in
 Table.SelectRows(Table, each [Index] <= varIndex and [Month Number] = VarMonth and [Category] = varCategory))

 

Zubair_Muhammad - followup to this. How would you assign two variables? So in the above, say I needed to know the year and account number.

 

 

 

each let myyear=[Year] and let account=[szMainAccount]
in
Table.SelectRows([All Rows], each [Year] >= myyear and [szMainAccount]= account))

 

 

 

You can see my logic, but I've tried various ways to do it and nothing works on the variable assignment. I just get errors. Any guidance?

artemus's avatar
artemus
Microsoft Employee
6 years ago

Option 1:

 

each 
   let 
       myyear=[Year],
       account=[szMainAccount]
    in
       Table.SelectRows([All Rows], each [Year] >= myyear and [szMainAccount]= account))

 

Option 2:

 

 

each Table.SelectRows([All Rows], (x) => [Year] >= x[Year] and [szMainAccount]= x[szMainAccount])

 

Note: "each" is shorthand for "(_) =>"

and [Foo] is shorthand for _[Foo]

 

Also:

table[[Foo], [Bar]] => Table.SelectRows(table, {"Foo", "Bar"})

table{0} => First row

table{[Foo = "Bar"]} => Get the row where column Foo has value "Bar", error if not exactly 1 row.

{0 .. 9} => A list from 0 to 9

table{1}? => Get the second row, or null if table does not have 2 rows

table{0}[Name] => Get the value from Name column for the first row

 

Finally, most powerful, complex funcation in  Power query:

Table.View