Forum Discussion

Miguel_Rojo's avatar
Miguel_Rojo
Helper II
2 years ago
Solved

Power Query - Anomaly - Transformations truncate my dataset

Hello Community! I am currently facing anomalous behavior when trying to perform transformations in Power Query. I attach a description of the phenomenon, hoping I can receive support from you to so...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Miguel_Rojo 

     

    First you can try using a tool to see the actual query submitted to the PostgreSQL server. Check if there is any filtering rows statement received there. 

    Is there a PostgreSQL equivalent of SQL Server profiler? - Stack Overflow

     

    Then you may try removing the transformation steps made by Power Query and check if this can bring all rows back.

     

    Can you share the Power Query M code that's from the Advanced Editor after removing sensitive information? 

     

    Best Regards,
    Jing

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Miguel_Rojo 

 

First you can try using a tool to see the actual query submitted to the PostgreSQL server. Check if there is any filtering rows statement received there. 

Is there a PostgreSQL equivalent of SQL Server profiler? - Stack Overflow

 

Then you may try removing the transformation steps made by Power Query and check if this can bring all rows back.

 

Can you share the Power Query M code that's from the Advanced Editor after removing sensitive information? 

 

Best Regards,
Jing

  • Miguel_Rojo's avatar
    Miguel_Rojo
    Helper II
    2 years ago

    Hi Anonymous ,

     

    Thanks for the advice!,

    The problem was caused by the "Query Folding" feature. 

    I haven't realized it was ebabled. I proceeded to desable this feature, and all the data was correctly loaded:

     

    I'll accept your advide as solution; but I'd like to make a final question (hope you can help me):

    • Scenario: 

      1. Once I connect to the PostgreSQL database, I successfully load the information I need (having disabled "Query Folding", I am able to load the 3.3 million rows).

      2. Subsequently, I perform a series of transformations (screenshot attached), until I reach a specific one: I perform a LEFT JOIN, in which I add to my main table (the one that contains 3.3 million rows) two extra columns: "Date" and " City" (these last two columns are extracted from another table, and a "Merge" is carried out of those that match by email:

                                     

                                     

    The LEFT JOIN increases the dataset of my "3.3 Millions Table" to 3.5 Millions aprox; but Power Query has an error and I can not perform a refresh, because different errors appears:

     

    Do you know if Power Query can not perform MERGES with high volume of data?, or if another issue is causing the error?