Native Query

[Freya]

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

[Freya]

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] )

[Freya · Accepted solution]

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] )

[amitchandak]

@Freya , Strange , Try like

select * from public.address 
where "ID"  in (select max("ID") from public.address a 
group by "Enterprise_Nbr")

[Freya]

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