locked
Power Query - Parameters Table RRS feed

  • Question

  • Hi,

    I want to build an SQL query that contains a value from paramters table.

    So for example:

    select *
    from TestTable
    where Project= ProjectValue
    and ID=IDValue

    and ProjectValue and IDValue are taken from external table.

    How can I do it?

    Thanks!


    lekfir

    Sunday, October 11, 2015 9:53 PM

Answers

  • Monday, October 12, 2015 6:01 AM
  • Power query performs what is known as query folding, which means that will it will attempt at pushing back filtering actions to the server. Providing a query text rather than using M code may not always yield the performance improvement that you assume.

    Yet, if you want to stick with providing a query text, there is a way to build it dynamically and use inputs from other queries. You do not specify which type of database you are connecting to, but assuming you are connecting to SQL Server, the statement that is generated when you supply the query text is in the following form

    Sql.Database("localhost", "Northwind", [Query= select * from Customers])
    This highlights the fact that the query text is passed as a record to the Sql.Database function. Assuming that your parameters are given by the first row of a table named 'params', the following code builds the query text dynamically:

    let
        QueryRecord = [Query="select * from TestTable where Project=ProjectValue and ID=IDValue"],
        paramIDValue = Record.TransformFields( QueryRecord,{{"Query",each Text.Replace (_, "IDValue", params{0}[IDValue])}}),
        paramProjectValue = Record.TransformFields( paramIDValue,{{"Query",each Text.Replace (_, "ProjectValue", params{0}[ProjectValue])}}),	
        Source = Sql.Database("myhost", "mydB", paramProjectValue )
    in
        Source


    Wednesday, October 14, 2015 10:21 AM

All replies

  • Monday, October 12, 2015 6:01 AM
  • Thanks Imke Feldmann,

    This article uses the filter option. That means I will load all the data and only after than filter it inside the excel.

    From performance aspect, I must  put the parameter inside the sql query.

    Filtering 150K rows is problematic.

    Any other solution?

    Thanks!


    lekfir

    Tuesday, October 13, 2015 9:12 PM
  • Power query performs what is known as query folding, which means that will it will attempt at pushing back filtering actions to the server. Providing a query text rather than using M code may not always yield the performance improvement that you assume.

    Yet, if you want to stick with providing a query text, there is a way to build it dynamically and use inputs from other queries. You do not specify which type of database you are connecting to, but assuming you are connecting to SQL Server, the statement that is generated when you supply the query text is in the following form

    Sql.Database("localhost", "Northwind", [Query= select * from Customers])
    This highlights the fact that the query text is passed as a record to the Sql.Database function. Assuming that your parameters are given by the first row of a table named 'params', the following code builds the query text dynamically:

    let
        QueryRecord = [Query="select * from TestTable where Project=ProjectValue and ID=IDValue"],
        paramIDValue = Record.TransformFields( QueryRecord,{{"Query",each Text.Replace (_, "IDValue", params{0}[IDValue])}}),
        paramProjectValue = Record.TransformFields( paramIDValue,{{"Query",each Text.Replace (_, "ProjectValue", params{0}[ProjectValue])}}),	
        Source = Sql.Database("myhost", "mydB", paramProjectValue )
    in
        Source


    Wednesday, October 14, 2015 10:21 AM