Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 Help
  • smpa01's avatar
    smpa01
    6 years ago

    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

     

     

     

smpa01's avatar
smpa01
Community Champion
6 years ago
Change last line --- in Source

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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?