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

     

     

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.