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

     

     

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.