Forum Discussion

Analitika's avatar
Analitika
Post Prodigy
5 years ago

Power BI connection with PostgreSQL

Hello,

 

I would like to ask about connection of Power BI with PostgreSQL. 

 

I tried these but helped, still getting errors.

postgres: 

Source = PostgreSQL.Database("server", "db_name", [Query="select …. "])

 

to ODBC:

Source= Odbc.Query("driver={PostgreSQL Unicode(x64)};server=server_name;port=5432;database=db_name", "select … ")

 

or (if not SQL statement used)

Source= Odbc.DataSource("driver={PostgreSQL Unicode(x64)};server=server_name;port=5432;database=db_name",...

 

What queries should be added?

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Analitika , Not getting what you trying here.

     

    The best way I do this. I create a new connection with what I need and then replace it with the existing connection by checking the steps.

    • Analitika's avatar
      Analitika
      Post Prodigy
      4 years ago

      None of this helped me. I tried PostgreSQL.Database..

  • Icey's avatar
    Icey
    Community Support
    4 years ago

    Hi Analitika ,

     

    Try this:

     

    let
    Source = Value.NativeQuery(PostgreSQL.Database("server", "da_name"), "SELECT * FROM public.""Employee""", null, [EnableFolding=true])
    in
    Source

     

     

    Note: "Employee" is just a blank table used for test. And the table next to "public" needs another quotes.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Analitika's avatar
      Analitika
      Post Prodigy
      4 years ago

      I am getting the error with postgresql in advanced editor in Power BI.

      DataSource.Error: ODBC: ERROR [42601] ERROR: syntax error at or near ".";
      Error while executing the query
      Details:
      DataSourceKind=Odbc
      DataSourcePath=dsn=dbname

      OdbcErrors=[Table]

       

      Query is here:

      SELECT rws.zu0_kod AS zu0_kod,
      rws.did_nr AS did_nr,
      rws.adm_var AS adm_var,
      rws.did_dat AS did_dat
      ,COUNT (1) AS eil.skaicius
      FROM (SELECT tab1.zu0_kod,
      tab1.did_nr,
      tab1.adm_var,
      tab1.did_dat
      FROM (SELECT id_vidp AS id_vidp2,
      zu0_kod AS zu0_kod,
      AS did_nr,
      adm_var AS adm_var,
      did_dat AS did_dat
      FROM a.vidp
      WHERE a.vidp.did_dat >= filterdate) tab1
      LEFT OUTER JOIN a.vidp tab2
      ON tab1.id_vidp2 = tab2.id_vidp) rws
      GROUP BY rws.zu0_kod,
      rws.did_nr,
      rws.adm_var,
      rws.did_dat

      • FarhanAhmed's avatar
        FarhanAhmed
        Community Champion
        4 years ago

         

        it seems that your inner query contains error as no column selected in it.

        SELECT rws.zu0_kod AS zu0_kod,
rws.did_nr AS did_nr,
rws.adm_var AS adm_var,
rws.did_dat AS did_dat
,COUNT (1) AS eil_skaicius
FROM (SELECT tab1.zu0_kod,
tab1.did_nr,
tab1.adm_var,
tab1.did_dat
FROM (SELECT id_vidp AS id_vidp2,
zu0_kod AS zu0_kod,

[ColumnName is MISSING HERE] AS did_nr,


adm_var AS adm_var,
did_dat AS did_dat
FROM a.vidp
WHERE a.vidp.did_dat >= filterdate) tab1
LEFT OUTER JOIN a.vidp tab2
ON tab1.id_vidp2 = tab2.id_vidp) rws
GROUP BY rws.zu0_kod,
rws.did_nr,
rws.adm_var,
rws.did_dat