Forum Discussion
Include Parameters within Power BI to Query a SQL Data Source
Hi,
I have a SQL database I'm using as a datasource. The database is large and I would like to include two separate parameters within Power BI in an effort to query the database down to size. I need these parameters because the SQL database is pulling in records for many different lines of business. The parameters I create in Power BI will allow me to specify what lines of business I'm including in Power BI. This will allow me to reuse what I create in Power BI over and over again for different business lines.
I've already linked the SQL database properly to Power BI and I have data.
I've already created two separate parameters in Power BI, 1) Underwriter # and 2) Group #:
I am struggling to incorporate these two parameters in with the existing 'Get Data' script/syntax Power BI generated:
I do not know where I should include my two Where clauses...where I can specify the specific 1) Underwriter #(SQL) and 2) Group #(SQL) criterias. Both fields in SQL are set to text and I made both Parameters are set to recognize text as well.
I think the alteration/syntax edit should include something like this: WHERE PUNBR = '"&Underwriter #(SQL)&'" AND GRNBR = '"&Group #(SQL)&"'
...but I'm not really sure. I've never attempted to do this before.
In case it helps:
1) PUNBR is the field in the SQL Database I've referred to as Underwriter #(SQL)
2) GRNBR is the field in the SQL Database I've referred to as Group #(SQL)
Thank you for the help!
Anonymous
let's suppose your
server = x
database = y
table name = z
sample sql query is (as you would put it on SSMS)
select *
from z
WHERE [period_number]=2
AND [fiscal_year]=2020when you put this on a M
it becomes this
let Source = Sql.Database("x", "y", [Query="select *#(lf)from zs#(lf)WHERE [period_number]=2#(lf)AND [fiscal_year]=2020"]) in SourceNow if you want to pass two parameters to the where clauses as P1 and P2, (P1=2020, P2=2)make sure they are text first
you can create a custom query as below
(x as text, y as text)=>let Source = Sql.Database("x", "y", [Query="select *#(lf)from z#(lf)WHERE [period_number]="&P2&"#(lf)AND [fiscal_year]="&P1&""]) in Source
10 Replies
Anonymous
let's suppose your
server = x
database = y
table name = z
sample sql query is (as you would put it on SSMS)
select *
from z
WHERE [period_number]=2
AND [fiscal_year]=2020when you put this on a M
it becomes this
let Source = Sql.Database("x", "y", [Query="select *#(lf)from zs#(lf)WHERE [period_number]=2#(lf)AND [fiscal_year]=2020"]) in SourceNow if you want to pass two parameters to the where clauses as P1 and P2, (P1=2020, P2=2)make sure they are text first
you can create a custom query as below
(x as text, y as text)=>let Source = Sql.Database("x", "y", [Query="select *#(lf)from z#(lf)WHERE [period_number]="&P2&"#(lf)AND [fiscal_year]="&P1&""]) in SourceThank you for the help.
I tried implementing what you suggested and updated my M accordingly but it errored out. Here is my current M that is erroring out:
(D01PSQLENTA01 as text, ECI as text)=>let
Source = Sql.Database("D01PSQLENTA01", "ECI",
[Query="select *#(lf)from NovaDailyEligibilityExtract#(lf)WHERE [PUNBR]="&Underwriter #(SQL)&"#(lf)AND [GRNBR]="&Group #(SQL)&""]
in
dbo_NovaDailyEligibilityExtractError:
Both of my created parameters are set as text:
I'm sure my M is incorrect here.
- Can you change the parameter name to parameter1 or parameter#1 from parameter # 1
The simplest way to do this is to use the UI to filter the your fields and select any single value. The resulting Filter step can then easily be edited, where you replace the string of the single value you selected with your parameter. The step would look something like if you use the UI to filter a field.
Table.SelectRows(dbo_NovaDailyEligibilityExtract, each [PUNBR] = "123" )And you would alter it to
Table.SelectRows(dbo_NovaDailyEligibilityExtract, each [PUNBR] = #"Underwriter #(#)(SQL) (201)")Power Query will convert this into SQL statements on the backend, you can right click on the step and click "View Native Query" to see this.
great ...GRNBR has probably varchar as the column data type in native DB.
you would normally query in the native DB as
AND GBNBR]='06N'I am glad you managed to adapt it to your solution.
