Forum Discussion
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...
If i understand the problem correctly
each let myyear=[Year] in Table.SelectRows([All Rows], each [Year] >= myyear))
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?
Thank you so much. This is the solution I've been searching for hours. Didn't know let could be used inside a function.