Connecting to Data: PostgreSQL (Function) error

[kcdavi01]

Hi all, I hope all is well! I have a client that would like to connect to a PostGreSQL function (vs a table) but we're running into an error message when trying to connect via a Power Query M script:

• The PostGresSQL function is: select * from [FUNCTION](?,?,?,?)
• The parameters are:
  • From
    • (Today's Date (Calendar Pick))
  • To
    • (Today's Date (Calendar Pick))
  • DataType
    • Received
    • Transaction
    • Scheduled
    • Staged
    • Validated
    • Loaded
  • Status
    • Passed
    • Failed
    • All
    • In Progress
    • Pending
  • Source Name
    • Comes from another table in PostgresSQL
    • SELECT source_name FROM source WHERE active_flag = TRUE ORDER BY source_name ASC

• I tried putting it into Power Query M:
• Script:

let

    StartDate = "01/01/2026",

    EndDate = "01/01/2026",

    DataType = "Received",

    Status = "Passed",

    Source = PostgreSQL.Database("SERVER", "DATABASE", [Query="SELECT * FROM FUNCTION('" & StartDate & "', '" & EndDate & "', '" & DataType & "', '" & Status & "'')"])   

in

    Source

• But I got the following error
• Error message:

DataSource.Error: PostgreSQL: 42601: unterminated quoted string at or near "'Passed'')"

Details:

    DataSourceKind=PostgreSQL

    DataSourcePath=SERVER

    Message=42601: unterminated quoted string at or near "'Passed'')"

    ErrorCode=-2147467259

• Screenshot:
• 

What should I enter in order to access the data? Is it safe to assume I need a custom Power Query M script vs using the standard PostGresSQL connector?

Thanks a bunch in advance!

[MFelix · Accepted solution]

Hi @kcdavi01 ,

Believe that you problem is with the syntax of the Passed where you have added and additional '

Try the following:

PostgreSQL.Database("SERVER", "DATABASE", [Query="SELECT * FROM FUNCTION('" & StartDate & "', '" & EndDate & "', '" & DataType & "', '" & Status & "')"])   

[kcdavi01]

Hi Miguel,

Hope all is well! Thanks a bunch for providing that solution! I was able to final get past that error message!

I did have a bit of a follow-up if you can address of course:

• My original script had very static values for its parameters. I was wondering how I could target everything , like for example all of the Status options vs just 1
• Passed
• Failed
• All
• In Progress
• Pending

Is there someway I can get all the data or would I have to write out each one? And ifs its to be written out, what should the format be?

Thanks again for your help!

[MFelix]

Hi @kcdavi01 ,

Not sure how you have everything setup and how the coede works in terms of the PostGres SQL but one option can be to turn the initial SQL statment into a table and use the values on the table to get the SQL query to run follow these steps:

• Create a table with the status column
• Now add a new column to that table with the following code:

let
    StartDate = "01/01/2026",
    EndDate = "01/01/2026",
    DataType = "Received",
    Status = [Status],
    Source = PostgreSQL.Database("SERVER", "DATABASE", [Query="SELECT * FROM FUNCTION('" & StartDate & "', '" & EndDate & "', '" & DataType & "', '" & Status & "')"]) 


in

    Source

If you see the Status value is now replaced by the Column you use rthen you can just expand your new column with all your data:

Another option is to do a function and use it on the new column:

let
    ParameterValues = (Status as text)  =>
 let

    StartDate = "01/01/2026",

    EndDate = "01/01/2026",

    DataType = "Received",


    Source = PostgreSQL.Database("SERVER", "DATABASE", [Query="SELECT * FROM FUNCTION('" & StartDate & "', '" & EndDate & "', '" & DataType & "', '" & Status & "')"]) 

in

    Source

    in ParameterValues

Then add the custom function:

The rest is equal.

Using a function instead of writing the code directly on the table is easier for maintenance in the future.