DirectQuery error when loading visualization from PostgreSQL table using Dynamic M query parameter

[Anonymous]

I'm trying to create a line graph in Power BI desktop from an on-premises PostgreSQL database table via DirectQuery while using a Dynamic M query parameter, and the visualization fails to load. I get the following error message when I click the See details link:

I'm able to successfully load the table while using the Dynamic M query parameter in Power Query editor (see image below), so I don't understand why it fails to load in the visualization. Regarding the query parameter, I created a new table in Power BI that lists some example project ID's to use as a slicer and then created matching parameters using instructions from this Medium article:

So, my question is if Power BI visualizations support PostgreSQL data pulled via DirectQuery with Dynamic M parameters. Assuming they do, how can I resolve this error? And if not, what databases do support this functionality?

Thank you in advance!

[Anonymous]

I'm happy to report that using Value.NativeQuery with the [EnableFolding = true] option enabled resulted in the visualization loading successfully:

let
    Source = PostgreSQL.Database("Insert IP address here", "Insert database name here"),
    Query = Value.NativeQuery(
        Source,
        "SELECT * FROM foo1.test WHERE proj = " & Text.From(ProjID_Parameters) & "",
        null,
        [EnableFolding = true])
in
    Query

Before including the EnableFolding option, the table would load fine in Power Query Editor but the same visualization error occurred. I stumbled upon a blog post that discussed passing parameters via query folding, along with why EnableFolding needed to be included.

Thanks for the assistance!

[Anonymous]

The SQL statment (as it's written in Power Query) is correct, because even though the value in the proj column is being called via the Text.From function, it's actually an integer and not a text value. I have to use the Text.From function because the entire SQL query is itself a string, so using the Number.From function results in an error due to the conflicting data types:

Out of curiosity, I tried both of the following lines of code in DBeaver, and they both successfully loaded the table. I assumed that using the single quote around an integer value would result in it being cast as a text datatype and result in an error, but it apparently doesn't:

select * from foo1.test where proj = '12345';

select * from foo1.test where proj = 12345;

I've also confirmed that inserting a single quote before and after the double quote while using the Text.From function will result in the table being loaded just the same as the original SQL query, but leads to the same error in the visualization. So, I'm still left with the same error as before regardless if I use a sinqle quote or not.

Thank you for the response.

[lbendlin · Accepted solution]

Instead of using the Query option directly I recommend you try the Value.NativeQuery variation. That may make it easier to consume the parameter.

[lbendlin]

Your SQL code is missing the single quotes around the text value.