Include Parameters within Power BI to Query a SQL Data Source

[Anonymous]

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]

Solved the issue:

Correct M:

let
Source = Sql.Database("D01PSQLENTA01", "ECI",
[Query="SELECT * #(lf)FROM [NovaDailyEligibilityExtract] #(lf)WHERE PUNBR = '"&UnderwriterSQL&"'#(lf)AND GRNBR = '"&GroupSQL&"'"])
in Source

[smpa01]

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.

[smpa01 · Accepted solution]

@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]=2020

when 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
    Source

Now 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

[Anonymous]

Thank 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_NovaDailyEligibilityExtract

Error:

Both of my created parameters are set as text:

   

I'm sure my M is incorrect here.

[Anonymous]

I noticed I had several issues with the M code.  I made those small changes but still have an error.

Current M:

(D01PSQLENTA01 as text, ECI as text)=>let
Source = Sql.Database("D01PSQLENTA01", "ECI",
[Query="select *#(lf)from dbo_NovaDailyEligibilityExtract#(lf)WHERE [PUNBR]="&UnderwriterSQL&"#(lf)AND [GRNBR]="&GroupSQL&""])
in
dbo_NovaDailyEligibilityExtract

I also updated the names of my parameters to UnderwriterSQL and GroupSQL.

[smpa01]

Change last line --- in Source

[Anonymous]

Alrighty.  Making progress here and thank you for walking me through this.

So with the last edit you mentioned it ran but I made one change.  I changed the =>let line from:

(D01PSQLENTA01 as text, ECI as text)=>let

to

(UnderwriterSQL as text, GroupSQL as text)=>let

I think this is correct?  We were previously listing the served and database as text.  We are now listing both perameters as text.

With that out of the way (I think), my new M is as follows:

(UnderwriterSQL as text, GroupSQL as text)=>let
Source = Sql.Database("D01PSQLENTA01", "ECI",
[Query="select *#(lf)from dbo_NovaDailyEligibilityExtract#(lf)WHERE [PUNBR]="&UnderwriterSQL&"#(lf)AND [GRNBR]="&GroupSQL&""])
in Source

I now have this as the interface:  

However, when I enter 201 for UnderwriterSQL and 06N for GroupSQL I now get this error:

Am I setting the correct fields as text in the (UnderwriterSQL as text, GroupSQL as text)=>let line?  Is that the issue?  

[smpa01]

Can you change the parameter name to parameter1 or parameter#1 from parameter # 1

[pdelia]

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.

[Anonymous]

I don't know what you mean by "use the UI to filter".  I googled it and I've gone in to settings and "Enabled it.":

Do you mean to simply go in to Edit Query and add a step where I filter for the two criterias I need?

I do not know what the 'UI' is you are referring to??