Power query dynamic filter between two values

[gargamalebarbosa]

Hi ,
Ihave a table like that. I need a dynamic filter in power query.
İ want to filter "Point" column with min/max criterias in cells F1- F2 values.

Thank you,

File Link

File link

 

[jdellasala]

Very unclear, especially since the Min and Max are wrong.
That said, the PDF breaks the table into 2 pages. Page 1 has 4 columns as shown above with no columns separating, and page 2 with only the first 2 columns of page 1.

Using Power query it's simple enough to combine both tables into a single table of 62 rows, but the Min Max in the PDF is not just inaccurate but unneeded. Those values can be extracted from the main table and loaded as shown above, but as I said it's unclear what you want your end product to be.

Please use XL2BB when posting data.

 

[jdellasala]

So here's the solution.

Book1
	A	B	C	D	E	F	G	H	I
1	Personnel	Points		Min		Max		Personnel	Points
2	Gage Lavoie	65		25		75		Gage Lavoie	65
3	Shyanne Blaney	36						Shyanne Blaney	36
4	Alfonso Crawford	93						Ayden Hough	38
5	Ayden Hough	38						Shayla James	64
6	Tony Breton	83						Derick Ferreira	41
7	Tristan Roderick	76						Jenny Cranstoun	72
8	Shayla James	64						Giancarlo Bustos	63
9	Taniya Graham	85						Payton Byington	75
10	Derick Ferreira	41						Ayana Rodríguez	39
11	Jenny Cranstoun	72						Angel Peterson	33
12	Giancarlo Bustos	63						Franklin Dalton	53
13	Payton Byington	75						Quinn Roderick	27
14	Daniella Collins	95						Omari Rynders	33
15	Ayana Rodríguez	39
16	Angel Peterson	33
17	Franklin Dalton	53
18	Quintin Durham	94
19	Quinn Roderick	27
20	Tessa Clough	79
21	Omari Rynders	33
Sheet3

Blue tables are manually created tables, green table is from Power Query.

Put the Min and Max values in their own tables. Bring each in and just Drill Down to the value. The Query names you use are now the values in the Table Cell. Then bring in the table, filter it manually using "Between", then replace the minimum value in the M Code with the name of the Minimum Query, and then the maximum value in the M Code with the name of the Maximum Query:

let
    Source = Excel.CurrentWorkbook(){[Name="tblPoints"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Personnel", type text}, {"Points", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Points] >= tblMin and [Points] <= tblMax )
in
    #"Filtered Rows"

Code for the Minimum Table - Query named tblMin:

let
    Source = Excel.CurrentWorkbook(){[Name="tblMin"]}[Content],
    Min = Source{0}[Min]
in
    Min

Same thing for Query tblMax.

Change the values in the Min and Max tables and Refresh All to update. You CAN make the Min and Max value cells use a Data Validation list to narrow the scope of the two variables as well.

You could also use Data Validation in a single cell with a Named Range and pull just that in, but I wanted to keep this simple for now.