Forum Discussion
Native Query
Hi,
I'am a newbi to PBI and I have a question.
I am using PowerBI Desktop which is linked to a PostgreSQL-database. There are very large tables so I would like to limited the linked tables with DirectQuery and an SQL statement.
Get Data > PostGreSQL database > Data Connectivity Mode "DirectQuery" added the sql statement in the Advanced Options
select * from public.address
where "ID" = (select max("ID") from public.address a
group by "Enterprise_Nbr")
This doesn't work, I get the error below.
If I try another query: select * from address where Enterprise_Nbr = "x" this works.
I get that there is a problem with the subquery but how do I solve this?
Thanks
Found it 🙂
= Value.NativeQuery(PostgreSQL.Database("000.000.00.0:5432", "kbo"), "select * from address where ""ID"" in (select max(""ID"") from address group by ""Enterprise_Nbr"")", null,[EnableFolding=false] )
4 Replies
Freya , Strange , Try like
select * from public.address
where "ID" in (select max("ID") from public.address a
group by "Enterprise_Nbr")I think I am a little bit closer now. 👍 I get an other error message (see below). From other posts I understand that you can change the EnableFolding option in Transform Data / Source. But I can't seem to get there.
If I click "Retry" I get the same message (seems logical), if I click "Edit" than I get the startscreen from GetData where there isn't an option to enablefolding.
I am using a blank pbix-file without any links, imports.https://learn.microsoft.com/en-us/power-query/connectors/postgresql
I figured out how to enable the query folding but the downside is that I have to convert the sql statement manually.
This one works:
= Value.NativeQuery(PostgreSQL.Database(".000.000.00.0:5432", "kbo"), "select * from address", null,[EnableFolding=false] )But when I try the query that I need I get an error. I can't seem to find the error in the syntax.
= Value.NativeQuery(PostgreSQL.Database("000.000.00.0:5432", "kbo"), "select * from address#(lf)where ""ID"" in (select max(""ID"") from address a group by ""Enterprise_Nbr""", null,[EnableFolding=false] )Found it 🙂
= Value.NativeQuery(PostgreSQL.Database("000.000.00.0:5432", "kbo"), "select * from address where ""ID"" in (select max(""ID"") from address group by ""Enterprise_Nbr"")", null,[EnableFolding=false] )
