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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Laser_Tim_88
Advocate I
Advocate I

Start and End Date Parameters for SQL statement in Import mode

My IT associate and I recently configured a Direct Query in Power BI that leverages a SQL Function with parameterized Start and End Dates for my Reports. I've received authorization to bypass the SQL Function and execute queries directly from within Power Querys Advanced Editor. Hopfully, I can configure this to operate in import mode as I have more data cleansing operations I need done. I am encountering challenges in locating the appropriate syntax for inserting the date parameters within the advanced editor.

In the first Image I have the Function I currently use. 

 

Function.png

 In the second is the Statement the function uses.

Query.png


Your guidance on this matter would be greatly appreciatted! 

1 ACCEPTED SOLUTION

Sorry for the late reply. Hopefully you managed to figure it out, but if not then you need to go into the Model view, then bind the column to a parameter. Please note that this will only work in you have Direct Query connection. And ONLY Direct Query. If you have a mix of SharePoint, or anything else, it won't be available.

View solution in original post

3 REPLIES 3
Laser_Tim_88
Advocate I
Advocate I

@peterg0417 
I was able to get the Paramaters to work with the following before the Query

 

StartDate = Text.From ( StartDateParam ),
   EndDate = Text.From (  EndDateParam ),

 

& this in it

 

WHERE (h.Date_Entered_dt >= N'" & @StartDate & "' and h.Date_Entered_dt <= N'" & @EndDate & "' )

 

 

 This is great! How can my Start date and End Date Slicers update those paramaters like they do with the Direct Query?

Sorry for the late reply. Hopefully you managed to figure it out, but if not then you need to go into the Model view, then bind the column to a parameter. Please note that this will only work in you have Direct Query connection. And ONLY Direct Query. If you have a mix of SharePoint, or anything else, it won't be available.

peterg0417
Helper II
Helper II

peterg0417_0-1697651810347.png

 

 

Try without the @  

 

That's how I have it in one of my queries.

 

edit: Oh, and StartDate and EndDate are separate parameters:

peterg0417_0-1697651989763.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors