Dynamic filter power query excel

Good day
I am using power query in Excel for the first time
I am trying to change a static filter to a dynamic filter for both the column name to be filtered and the value.
So far I have managed to create a parameter table in excel and make the following changes to the syntax:
let
Source = Excel.Workbook(File.Contents("C:\Data YM\All members profile data YM.xlsx"), null, true),
Search_criteria_Parameter = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],
pColumn_name = Search_criteria_Parameter{0}[Name of parameter],
pColumn_value = Search_criteria_Parameter{0}[Value],
#"Data YM_Sheet" = Source{[Item="Data YM",Kind="Sheet"]}[Data],
#"Promoted Headers1" = Table.PromoteHeaders(#"Data YM_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Web_Site_Member_ID", Int64.Type},
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each pColumn_name = pColumn_value)
in
#"Filtered Rows"
I get no errors but no resultant rows are displayed.
When I replace pColumn_name with [Web_Site_Member_ID] the query works.
When I step through the applied steps the value for pColumn_name displays correctly as Web_Site_Member_ID.
Can you please assist- first time using power query.
Alwyn
Alwyn SchutteAsked:
Who is Participating?
 
Marcel BeugelsdijkCommented:
Try and replace:
each pColumn_Name = pColumn_value
by:
each Record.Field(_,pColumn_name) = pColumn_value
1
 
Alwyn SchutteAuthor Commented:
Dear Marcel
You are a star!
Works perfectly.
Thanks
Alwyn
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.