Forum Discussion

yashsam's avatar
yashsam
Copper Contributor
Jun 21, 2022

Power Query: create sql dynamic match on multiple columns

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...
power bi
Lorenzo's avatar
Lorenzo
Silver Contributor
Jun 22, 2022

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?

 

 

  • yashsam's avatar
    yashsam
    Copper Contributor
    Jun 22, 2022
    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.
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Jun 22, 2022
      yashsam
      Just updated my previous post with a revised version of the SqlQueryString query. Same output but a bit cleaner
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Jun 22, 2022

      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