Dynamic Query Parameter. Troubleshooting help

Power Query
1

Hi everyone,
My data source is a sql server and I think I need a dynamic query parameter so the user can use a slicer in order to filter the fact table by a date before performing the power query transformations.

Do dynamic query parameters work only on tables in direct query mode ?

I’m getting an error message:
“ LE DB or ODBC error: [Expression.Error] We cannot convert the value #datetime(2023, 2, 28, 0, 0, 0) to type Date”

And also this error message as soon as I bind the date field to the parameter:

image
image2778×1284 152 KB

2

Don’t know if this will resolve your issue but yeah for power query a Datetime value is not equal to a Date value, instead try this:

#date( 2023, 2, 28)

Here’s a link to the section on Considerations and limitations for Dynamic M query parameters

3

Hello @Schreg

Did the response from @Melissa help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

4

Hi @Melissa , thanks so much for your response. I got the Parameter to update the DirectQuery table based on a slicer selection on the report.
Here is how it looks in the query:
ParameterQuery

However, every time I change the date I receive this popup message asking to approve the new query:

parameterapproval
parameterapproval583×544 13.8 KB

Is there any way around this?
Does this dynamic query parameter require a Direct Query table?

5

Hi @Schreg,

Have a look in the Options, in the Security section
You can disable it there.

image
image675×650 32 KB

I hope this is helpful

6

Hello @Schreg

Did the response from @Melissa help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

7

Hi @Melissa , thanks for showing that settings option. One last question regarding this-- is it possible to do this on an import table? Or does it have to be a Direct Query table?

8

Dynamic M query parameters give model authors more control over the filter selections to incorporate into DirectQuery source queries. So yes, DirectQuery.