Table populated with subset of another Table, without using Pivot Tables

[k1s]

Thank you!

For future reference, how would I make it work with a value directly from a Table?

Obviously, I could name a cell in the table, or have a named cell reference a cell in a table, like the attached, but can it work from a structured reference?

 

[Chihiro]

You can, but there is no benefit in using Table, when you are referencing single value variable.

Unless you need to load list from single column. Just load the entire table to PowerQuery and do join operation as needed.

To load single column from table as list...

listName = Excel.CurrentWorkbook(){[Name="TableName"]}[Content][ColumnName]

 

[k1s]

Thanks,

I was thinking I might end up wanting to add another selection criterion at some point, but I suppose I could always create another named cell for another single variable.

Is there a simple way to get the query to automatically refresh (e.g. when the worksheet is activated)?

 

[Chihiro]

You set the option in Data connection property. Or write simple code. Either or.

 

[k1s]

Thanks, found the option to set the Data connection to refresh every time the workbook is opened, and towards the bottom of this page, an example of some code to refresh it.

 

[LeonW]

{=INDEX(Table1[Pupil_ID],SMALL(IF(Table1[Year]=$I$2,ROW(Table1[Year])-ROW(INDEX(Table1[Year],1,1))+1),ROWS($I$3:I3)))}

^^ Array Formula

Thank you very @PaulF - been hunting for this type of formula for a week - Most appreciated!