none
Power Query dynamic SQL statement based on cell in worksheet (Firewall Error) RRS feed

  • Question

  • I am trying to make it so a user can type a value into a cell, and my SQL query will use that value as a parameter. I got close but I get "Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

    My Advanced Editor looks like so:

    let

        SQL = "SELECT * from SCHEMA.TABLE where FIELD = ",
        SQL1 = Text.Combine({SQL,InfoFromWorksheet}),
        Source = Oracle.Database("servername", [Query=SQL1])
    in
        Source

    I was able to make the input cell a datasource in power query just fine (https://exceleratorbi.com.au/pass-excel-parameter-power-query/)

    Most of the online tutorials talk about loading the entire external datasource into Power Query, then doing some advanced filtering to only display the data wanted. I cannot load all of the datasource into Power Query and then filter there because my SQL is rather complex so a full return will taken minutes (yes the external datasources are slow too but that can't be fixed here). Because of this I need to inject or insert this parameter into the SQL so it only pulls a very small dataset

    I would rather the cell data of excel directly affect the SQL run against my server. Is this possible? How do I make the input from the Excel Worksheet not considered "another queries or steps"? I already messed with my privacy options and nothing helped.

    Using Excel 2016 64bit

    *** Answer - Change privacy options in multiple places, or change the code so the flatten of an excel cell happens in the same code that has the native query. See below


    Tuesday, June 19, 2018 3:13 AM

Answers

  • Here's the full write up on my solution for those searching for the answer in the future. There 2 similar but different objectives to consider

    1. Objective: Return / Query the entire dataset, then filter using cell data in Power Query
    2. Objective: Only return / query data for the cell data in a native query

    Which way you want is dependent on how you set up your data sources, and how large your dataset is. There are multiple paths to achieve each method

    1) Objective: Return / Query the entire dataset, then filter using cell data in Power Query


        a) https://exceleratorbi.com.au/pass-excel-parameter-power-query/


    2) Objective: Only return / query data for the cell data in a native query


        a) Use method 1a, and inject cell data into native query. Modify security and privacy permissions to disable firewall errors

        b) Use method 1a, and inject cell data into native query. Preform all steps of 1a in main query where you pull from the database (using advanced editor)

    For my case, my external data sources are slow, and my custom native SQL is complicated. Pulling in an entire dataset would take too long and possible be too large. Therfore for me I am going with option 2.

    *Note I am using Excel 2019 Professional Plus, your menus may differ. I am connecting to an Oracle Database

    1a) Return / Query the entire dataset, then filter using cell data in Power Query

    Follow steps located at https://exceleratorbi.com.au/pass-excel-parameter-power-query/. The quick outline of the steps is:

    1.        Convert Cell to Table - Enter your “parameter” into a cell. Highlight cell à format as table
    2.        Data source from Table – With new table still selected, Data Tab à Get & Transform à From Table
    3.        Flatten Table in Power Query – Right click value in the Power Query window for this new data source, Right Click on Cell à Drilldown
    4.        Setup your normal query you want to filter as normal (“Real Query”)
    5.        Click the sort / filter drop down on the real query, filter to first data option shown
    6.        Click advanced editor, change the filter step from the manual entry to the Table you created in step 3

    2a) Only return / query data for the cell data in a native query - Use method 1a, and inject cell data into native query. Modify security and privacy permissions to disable firewall errors

    1.        Follow 1a, steps 1-3
    2.        Create a new query from your database, so we can steal that connection string (I used a known small and quick reacting table, providing a select * from small_table) – Data Tab à New Query à From Database à Oracle à provide known quick sql into SQL statement at bottom of pop-up )you may have to expand advanced / other options near bottom
    3.        Edit query to bring up Power Query
    4.        Go to advanced editor for this dummy SQL – Home Tab à Query à Advanced editor. It should look something like this

    let
        Source = Oracle.Database("servername", [Query= “Select * from small_table where id= ‘123’ ”])
    in
        Source

    1.        Add line above source called SQL. Make it equal to the real SQL you want (e.g SQL = “Select * from small_table where id= ‘ “
    2.        Add line to combine SQL and variable SQL1 = Text.Combine({SQL,CellFromWorksheet})
    3.        Add line to add final quote for filter (required for my use case) SQL2 = Text.Combine({SQL1,” ‘ “})
    4.        Change query string to SQL2
    5.        Finalize code, commas after each line and correct “in statement”. Final code for me looks like:

    let
    
       SQL = “Select * from small_table where id= ‘ “,
       SQL1 = Text.Combine({SQL,CellFromWorksheet}),
       SQL2 = Text.Combine({SQL1,” ‘ “}),
       Source = Oracle.Database("servername", [Query= SQL2])
    
    in
    
       Source

    1.    Change data source permissions – Home tab à Data Sources à Data source settings, make them all organizational
    2.    Test code, if it doesn’t work continue
    3.    Upper left corner menu à options and settings à query options à privacy à ignore
    4.    Done! (note you may also want to turn on the requirement to approve new native queries)

    2b) Only return / query data for the cell data in a native query - Use method 1a, and inject cell data into native query. Preform all steps of 1a in main query where you pull from the database (using advanced editor)

    Follow steps 2a, 1-9, except go to the advanced editor on the table from the cell, and copy the code there. Now go into the main query and add what you copied to the top of the let statement. Be sure to change the line where you combine your prewritten text and the variable from the excel worksheet. Will look something like this

    let
    
       Data = Excel.CurrentWorkbook(){[Name="TableNameinExcel"]}[Content],
       #"Changed Type" = Table.TransformColumnTypes(Data,{{"ColumnName",Int64.Type}}),
       FinalInput = #"Changed Type"{0}[1],
       SQL = “Select * from small_table where id= ‘ “,
       SQL1 = Text.Combine({SQL, FinalInput }),
       SQL2 = Text.Combine({SQL1,” ‘ “}),
       Source = Oracle.Database("servername", [Query= SQL2])
    
    in
    
       Source


    Tuesday, June 19, 2018 9:02 PM

All replies

  • Hey,

    This thread can definitely help you. It's basically the same situation.

    Tuesday, June 19, 2018 4:42 AM
  • I edited the global and current workbook privacy options to always ignore, but that didn't help.

    *Edit - yes it did, there were multiple options to edit

    Tuesday, June 19, 2018 3:29 PM
  • Here's the full write up on my solution for those searching for the answer in the future. There 2 similar but different objectives to consider

    1. Objective: Return / Query the entire dataset, then filter using cell data in Power Query
    2. Objective: Only return / query data for the cell data in a native query

    Which way you want is dependent on how you set up your data sources, and how large your dataset is. There are multiple paths to achieve each method

    1) Objective: Return / Query the entire dataset, then filter using cell data in Power Query


        a) https://exceleratorbi.com.au/pass-excel-parameter-power-query/


    2) Objective: Only return / query data for the cell data in a native query


        a) Use method 1a, and inject cell data into native query. Modify security and privacy permissions to disable firewall errors

        b) Use method 1a, and inject cell data into native query. Preform all steps of 1a in main query where you pull from the database (using advanced editor)

    For my case, my external data sources are slow, and my custom native SQL is complicated. Pulling in an entire dataset would take too long and possible be too large. Therfore for me I am going with option 2.

    *Note I am using Excel 2019 Professional Plus, your menus may differ. I am connecting to an Oracle Database

    1a) Return / Query the entire dataset, then filter using cell data in Power Query

    Follow steps located at https://exceleratorbi.com.au/pass-excel-parameter-power-query/. The quick outline of the steps is:

    1.        Convert Cell to Table - Enter your “parameter” into a cell. Highlight cell à format as table
    2.        Data source from Table – With new table still selected, Data Tab à Get & Transform à From Table
    3.        Flatten Table in Power Query – Right click value in the Power Query window for this new data source, Right Click on Cell à Drilldown
    4.        Setup your normal query you want to filter as normal (“Real Query”)
    5.        Click the sort / filter drop down on the real query, filter to first data option shown
    6.        Click advanced editor, change the filter step from the manual entry to the Table you created in step 3

    2a) Only return / query data for the cell data in a native query - Use method 1a, and inject cell data into native query. Modify security and privacy permissions to disable firewall errors

    1.        Follow 1a, steps 1-3
    2.        Create a new query from your database, so we can steal that connection string (I used a known small and quick reacting table, providing a select * from small_table) – Data Tab à New Query à From Database à Oracle à provide known quick sql into SQL statement at bottom of pop-up )you may have to expand advanced / other options near bottom
    3.        Edit query to bring up Power Query
    4.        Go to advanced editor for this dummy SQL – Home Tab à Query à Advanced editor. It should look something like this

    let
        Source = Oracle.Database("servername", [Query= “Select * from small_table where id= ‘123’ ”])
    in
        Source

    1.        Add line above source called SQL. Make it equal to the real SQL you want (e.g SQL = “Select * from small_table where id= ‘ “
    2.        Add line to combine SQL and variable SQL1 = Text.Combine({SQL,CellFromWorksheet})
    3.        Add line to add final quote for filter (required for my use case) SQL2 = Text.Combine({SQL1,” ‘ “})
    4.        Change query string to SQL2
    5.        Finalize code, commas after each line and correct “in statement”. Final code for me looks like:

    let
    
       SQL = “Select * from small_table where id= ‘ “,
       SQL1 = Text.Combine({SQL,CellFromWorksheet}),
       SQL2 = Text.Combine({SQL1,” ‘ “}),
       Source = Oracle.Database("servername", [Query= SQL2])
    
    in
    
       Source

    1.    Change data source permissions – Home tab à Data Sources à Data source settings, make them all organizational
    2.    Test code, if it doesn’t work continue
    3.    Upper left corner menu à options and settings à query options à privacy à ignore
    4.    Done! (note you may also want to turn on the requirement to approve new native queries)

    2b) Only return / query data for the cell data in a native query - Use method 1a, and inject cell data into native query. Preform all steps of 1a in main query where you pull from the database (using advanced editor)

    Follow steps 2a, 1-9, except go to the advanced editor on the table from the cell, and copy the code there. Now go into the main query and add what you copied to the top of the let statement. Be sure to change the line where you combine your prewritten text and the variable from the excel worksheet. Will look something like this

    let
    
       Data = Excel.CurrentWorkbook(){[Name="TableNameinExcel"]}[Content],
       #"Changed Type" = Table.TransformColumnTypes(Data,{{"ColumnName",Int64.Type}}),
       FinalInput = #"Changed Type"{0}[1],
       SQL = “Select * from small_table where id= ‘ “,
       SQL1 = Text.Combine({SQL, FinalInput }),
       SQL2 = Text.Combine({SQL1,” ‘ “}),
       Source = Oracle.Database("servername", [Query= SQL2])
    
    in
    
       Source


    Tuesday, June 19, 2018 9:02 PM