Simple steps not supported in Direct Query - Postgres

[aramirez7]

Hi all,

For our reports in Direct Query we used to perform simple transformation in Power Query which did not break the query folding such as renaming, filtering, add new columns (simple columns).

Today the query folding is breaking because of Table.RenameColumns . We don't understand why? all of our reports used to support these simple transformations.

We are using Postgres as Datasource!

Please help 😥

[vanessafvg]

when you use direct query I am assuming power bi interpret that as your model is fully the way you want it to be.

Rather than doing a table rename in power query, why dont you use a sql statement and rename your columns in that statement?

[aramirez7]

Hey  @vanessafvg Thanks for getting back to me..

Well, we don't want to write SQL statements - We already have a lot of reports created using M for simple transformations. It was working fine last week, Power Bi was able to create native queries for those simple transformations.

[vanessafvg]

have you had a software update in the last week?  What has changed?

[aramirez7]

Actually, I don't recall when I did the update -  but yes I am using the last version (December)

[vanessafvg]

ok so I am just trying to understand the problem.

You were renaming columns in power query before now but suddenly today it started breaking.

Something needs to have changed?  Were you delayed in noticing after the last update?  Otherwise what created this problem when it was fine before?

[aramirez7 · Accepted solution]

I just had a call with Microsoft support, we don't understand why it stopped working but we found a way to work around this query folding breaking.

I changed my M code from:

//This code was breaking the folding

let
Source = PostgreSQL.Database(Server, DataBase),
dbo = Source{[Schema="public",Item="gl_summary_union"]}[Data],
#"Renamed Columns" = Table.RenameColumns(dbo,{{"YEAR(b.GL_TRXN_CRTD_DATE)", "Year"}})
in
#"Renamed Columns"

========================

To this:

//This code allows Power Bi to fold the query

let
Source = Value.NativeQuery(PostgreSQL.Database(Server, DataBase), "SELECT * FROM public.gl_summary_union", null, [EnableFolding=true]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"YEAR(b.GL_TRXN_CRTD_DATE)", "Year"}})
in
#"Renamed Columns"

The weird thing is that the first code worked for a couple of months!

Thanks @vanessafvg  for your help.

Have a great day

Alejandro 

[vanessafvg]

no problem glad its sorted