none
Pass dynamic Parameter using power query RRS feed

  • Question

  • I want to pass the dynamic paramter from user from excel file. The excel file will pull the data from sql database using below query. However the empdept needs to be in input parameter from user. How to add this paramter using power query so that user can just get the data by the required dept.

    For example in below query,

    Select empname,empid,empdept from emp where empdept = 'ABC'

    Tuesday, December 24, 2019 9:29 AM

Answers

  • Hi

    There are tens examples on this site...

    • In your querying workbook Name the cell where the user should enter the filtering value i.e. qryParam
    • In a query named i.e. UserInput drop the following code:
    let
        Source = Table.FirstValue(Excel.CurrentWorkbook(){[Name="qryParam"]}[Content])
    in
        Source

    then in your query => SELECT… empdept = UserInput

    If this solves your problem please Mark as answer (can help others) - Thanks

    Tuesday, December 24, 2019 10:15 AM

All replies

  • Hi

    There are tens examples on this site...

    • In your querying workbook Name the cell where the user should enter the filtering value i.e. qryParam
    • In a query named i.e. UserInput drop the following code:
    let
        Source = Table.FirstValue(Excel.CurrentWorkbook(){[Name="qryParam"]}[Content])
    in
        Source

    then in your query => SELECT… empdept = UserInput

    If this solves your problem please Mark as answer (can help others) - Thanks

    Tuesday, December 24, 2019 10:15 AM
  • And this innocently created parameter query, when used in an external query, is liable to cause a firewall error.
    Tuesday, December 24, 2019 6:42 PM
  • And this innocently created parameter query, when used in an external query, is liable to cause a firewall error.

    Hi Colin. I should have mention it - my bad. May I ask a question re. this?

    Now that I can query external source (SQL below)… Query UserInput

    let
        Source = Table.FirstValue(Excel.CurrentWorkbook(){[Name="qryParam"]}[Content])
    in
        Source


    Main query (example):

    let DbSource = Sql.Database("SQLSERVERNAME\INSTANCENAME", "DatabaseName"), tableAccounts = DbSource{[Schema="dbo",Item="Accounts"]}[Data], // MAKE SURE Privacy Level for Current Workbook + Source are the same, i.e. 'Organizational

    // OR read Chriss Webb's post...

    FilteredYear = Table.SelectRows(tableAccounts, each [Year_End] >= UserInput) in FilteredYear

    Privacy levels (Power Query) / Chriss Webb - Data Privacy Settings in Power BI/Power Query
    Behind the scenes of the Data Privacy Firewall



    • Edited by Lz._ Monday, March 2, 2020 3:38 PM
    Wednesday, December 25, 2019 5:30 AM