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
Icon for Community Champion rankCommunity Champion
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

 

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    6 years ago
    Can you change the parameter name to parameter1 or parameter#1 from parameter # 1
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      smpa01
      Icon for Community Champion rankCommunity 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?