Forum Discussion
Reference a column to filter a nested table in Power Query?
If i understand the problem correctly
each let myyear=[Year] in Table.SelectRows([All Rows], each [Year] >= myyear))
If i understand the problem correctly
each let myyear=[Year] in Table.SelectRows([All Rows], each [Year] >= myyear))
Awesome. Thanks. As I was working through it I was thinking if this were in DAX I'd just create a variable or use EARLIER() to get what I wanted. Didn't occur to me to use a variable in Power Query. I wouldn't have gotton the "each" statements right anyway...
Many thanks! Very elegant.
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?
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
Thank you so much. This is the solution I've been searching for hours. Didn't know let could be used inside a function.
hello, i have the same case. is there any better way to have the same output but with better perfomance...
for my data source, working on nested table like this take a lot of time.Unfortunately not. Nested tables can be a source of performance issues. Not really a big deal on a few thousand records, but when you get into the hundreds of thousands or millions, it can be a show stopper.
