Forum Discussion

alicia57's avatar
alicia57
Helper I
9 years ago
Solved

Use a parameter in a query in a MySQL database

Hello,   I would like to use a list of results from a first query in a second one.   I launch a first query on a SQL Server database. I click on a column "id" and "Add as New Query", then "New Pa...
Need Help
  • mmanwaring's avatar
    mmanwaring
    9 years ago

    Hi

    because tx_id is a series of id numbers you need to pass them into SQL in a format it expects.

    I used Text.Combine to create my parameter so it read '123','124','125' which is what i needed to pass to the SQL where clause. As you are using a number then i do not think you need the '' so you need to pass into SQL IN clause (123,123,123,123) etc if tx_id looks like 123,123,123,123 then you can concatenate it into your SQL like your example.

    So the key to solving this is to make sure that tx_id results look exactly what SQL requires.

    Regards

    Mike

mmanwaring's avatar
mmanwaring
Resolver I
9 years ago

Hello Alicia

I have got this to work by concatenating my parameter into the SQL

for example

 

where id = ' "& Parameter &" '

just be sure to drill down in power query so that your parameter is a single value. if you need multiple id then this will be different to create.

thanks

Mike

 

alicia57's avatar
alicia57
Helper I
9 years ago

Hello mmanwaring,

 

Thank you very much for your answer !

 

Indeed I need a list of id. Do you know what is the way to create it ?

 

Regards,

Alicia

  • mmanwaring's avatar
    mmanwaring
    Resolver I
    9 years ago

    Hi Alicia

    With SQL you need WHERE id IN ('id','id','id')  for example

    so how i got this to work was i created a table in excel which was a single column of values. I then queried that into power query and made the column text, this created the step #"Changed Type" by default.

    then add a custom column and turned it into a list. here is the exact code, Text.Combine allowed me to add the ' '  to each value

    let
        Source = Excel.CurrentWorkbook(){[Name="CustomerList"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerCode", type text}}),
        CustomerCode = "'"&Text.Combine(#"Changed Type"[CustomerCode],"','")&"'"
    in
        CustomerCode

     

    then SQL is WHERE id IN ("& CustomCode &")

    Regards

    Mike

    • alicia57's avatar
      alicia57
      Helper I
      9 years ago

      Hi Mike, 

       

      Thank you again for your answer but I want to automate all the work in Power BI without using Excel or another software.

       

      I have a first query launched in Power BI under a SQL Server database. From this result I have selected the column which I want to use in another query, convert into number (because in the other database on which I will launch the other query, this field is number type) and I click on "Add as New Query", then I have created my parameter "tx_id".

       

      My second query is launch on a MySQL database. In the advanced editor, I tried WHERE id IN ("& tx_id &") but I have the error message : "DataSource.Error: MySQL: This database function doesn't support the query option 'Query' with value 'null'." whereas I have values in my list.

       

      Regards,

      Alicia

      • mmanwaring's avatar
        mmanwaring
        Resolver I
        9 years ago

        Did you use Text.Combine to convert the query into the format SQL requires?