Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Noyer
Helper I
Helper I

How to Reference Parameter in ODBC Query

Hi All

 

I've got the following query and it works fine.  The lines below are from the advanced editor:

 

let
Source = Odbc.Query("dsn=DW", "SELECT * FROM mwrdw.dbo.as_sales WHERE fiscyear >= 2020 ")
in
Source

 

I'm trying to replace the fiscyear with a parameter value but I'm failing.  I've set up a new parameter ("Parameter1") with a current value of 2020.  I've then changed the text in the advanced editor to:

 

let
Source = Odbc.Query("dsn=DW", "SELECT * FROM mwrdw.dbo.as_sales WHERE fiscyear >= "Parameter1" ")
in
Source

 

I get a "Token Comma expected" error.  I've tried a couple of variations with no success.  The fiscyear field in the source contains four digit years stored as numbers.

 

I'm trying to set up a parameter that I can pass to the service allowing me to edit the fisc year there.  The table stores a huge amount of data (too much for the desktop application that gives a memory error when too many years are loaded) but Power BI service will handle this. 

 

If I can get the parameter to work I can then have a much smaller desktop filesize while I work but pass many more years to the file in the service..  I hope I've explained that right!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User
4 REPLIES 4
amitchandak
Super User
Super User

Hi

Thanks for the quick reply.  Using the link you suggested I amended the advanced editor to:

 

let
Source = Odbc.Query("dsn=DW", "SELECT * FROM mwrdw.dbo.as_sales WHERE fiscyear >= "&Parameter1&" ")
in
Source

 

However I now get the following error:

 

Expression.Error: We cannot apply operator & to types Text and Number.
Details:
Operator=&
Left=SELECT * FROM mwrdw.dbo.as_sales WHERE fiscyear >=
Right=2020

 

I don't know what that means..?

You are passing as text parameter, it needs number

That solved it!  When I put '2020in the parameter it worked - thanks!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors