How to select a date as a parameter for a direct query?

[danyeungw]

Hello,

I need to create a dashboard to let the users select a Report Date, and pass the date to a direct query to SQL Server.

I am looking for how to create and select a date as a parameter for a direct query. Hope to get help here.

What I have done below may not be in correct direction. 

I created a "Report Date" query that contains last 90 days. I also created a ReportDateParameter that used the "Report Date" query.

I created a direct query "where ld.ReportDate = '", each Date.ToText([ReportDateParameter],"MM/dd/yyyy"), "'".  That has 

Expression.Error: We cannot convert a value of type Function to type Text.
Details:
Value=[Function]

Thanks.
Type=[Type]

[JaromG]

It looks like you are trying to create a dashboard in Power BI that allows users to select a Report Date and pass that date as a parameter for a direct query to SQL Server. Based on the information you provided, it seems you are on the right track, but there might be a small issue in the way you are trying to pass the parameter. To create a parameter for a direct query in Power BI, you can follow these steps: Create a Parameter: Go to the Power Query Editor, click on "Home," and then select "Manage Parameters." Create a new parameter, let's call it "ReportDateParameter," and specify the data type as "Date/Time." Define a Date Range Query: Create a new query that generates the date range you want to use for the user selection. It seems you have already done this step by creating the "Report Date" query that contains the last 90 days. Set the Parameter Value: In the "Report Date" query, you can use the parameter you created ("ReportDateParameter") to allow the user to select a specific date. Instead of using each Date.ToText([ReportDateParameter],"MM/dd/yyyy"), you can simply reference the parameter like this: [ReportDateParameter]. Use the Parameter in the Direct Query: In your direct query, where you have "where ld.ReportDate = '", you can modify it to "where ld.ReportDate = '" & Text.From([ReportDateParameter], "MM/dd/yyyy") & "'". This will convert the date from the parameter to the appropriate text format for the SQL query. With these adjustments, your direct query should be able to use the selected date from the "ReportDateParameter" to filter data in SQL Server. If you encounter any issues after making these changes, please feel free to share the updated code or any error messages you receive, and I'll be glad to assist you further. Good luck with your Power BI dashboard!

[danyeungw]

Thanks JaromG,
I changed it to "where ld.ReportDate = '" & Text.From([ReportDateParameter],"MM/dd/yyyy") & "'".

Now I received another error 

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?