Power Query: create sql dynamic match on multiple columns

[yashsam]

Hi, I am looking to fetch data from DB using SQL containing a dynamic match on 5 columns from an input table in power query. Let me explain in details.

Say I have a table like the below one where user inputs to filter the data from SQL DB on multiple values. I am looking to write a SQL with a WHERE clause that is a combination of all the below matches.

City	Store	Category	Line
Chennai
Chennai	A100	B5
	A100	B9
		B6
		B6	102
			500
			607
London		B6
	A200		600

SELECT city, store, category, line

FROM sample_table

I am only working with PQ for about 2 weeks now, excuse my lack of knowledge if this is something straight forward. Thanks in advance.

[#2]

Bump.. need to solve this quickly

[#3]

Hi @yashsam 

To clarify "that is a combination of all the below matches", could you post the exact query string (inc. any simple/double quotes) you want to pass to SQL Server with the below input Table?

[#4]

Thanks for offering to help :)

SELECT * city, store, category, line
FROM sample_table
WHERE city = 'Chennai'
OR city & store & category = 'ChennaiA100B5'

Looking to add one condition per row

In this case I understand the rows returned by the second condition is a subset of the first one. But it I expect a minimum of 30 different combinations when going live. Any of the fields could be left blank.

[#5]

@yashsam 

with below inputs

the attached query generates string:

SELECT * City, Store, Category, Line
FROM sample_table
WHERE City = 'Chennai' 
   OR City & Store & Category = 'ChennaiA100B5' 
   OR Store & Category = 'A100B9' 
   OR Category = 'B6' 
   OR Category & Line = 'B6102' 
   OR Line = '500' 
   OR Line = '607' 
   OR City & Category = 'LondonB6' 
   OR Store & Line = 'A200600'

Note that the query generating the string (SqlQueryString) relies on function query CombineFieldValues. If necessary it's possible to move into SqlQueryString

[#6]

@yashsam
Just updated my previous post with a revised version of the SqlQueryString query. Same output but a bit cleaner

[#7]

@yashsam 

Re. Bump.. need to solve this quickly

No news. Suspect one of the proposals you got solved the issue

There's a Mark as response at the bottom of each reply you get. Clicking one helps those who search...