Forum Discussion

kcdavi01's avatar
kcdavi01
Frequent Visitor
5 months ago
Solved

Connecting to Data: PostgreSQL (Function) error

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!

Need Help
Tips and Tricks
Tutorial Requests
  • MFelix's avatar
    MFelix
    5 months ago

    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 & "')"])

     

     

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 months ago

    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's avatar
      kcdavi01
      Frequent Visitor
      5 months ago

      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's avatar
        MFelix
        Super User
        5 months ago

        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.