Forum Discussion

aramirez7's avatar
aramirez7
Frequent Visitor
5 years ago
Solved

Simple steps not supported in Direct Query - Postgres

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)...
Need Help
  • aramirez7's avatar
    aramirez7
    5 years ago

    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 

     

aramirez7's avatar
aramirez7
Frequent Visitor
5 years ago

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's avatar
vanessafvg
Community Champion
5 years ago

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

  • aramirez7's avatar
    aramirez7
    Frequent Visitor
    5 years ago

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

    • vanessafvg's avatar
      vanessafvg
      Community Champion
      5 years ago

      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's avatar
        aramirez7
        Frequent Visitor
        5 years ago

        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