Forum Discussion
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 solve it:
- Using a query to a PostreSQL database, I extract a table with user data and additional metadata. As seen in the following image, the table has a total of 2.8 million rows:
As you can see, I can import it successfully.
- However, when I perform some transformation in Power Query to this imported Dataset (change the Data Type, or add a second conditional column, for example), after clicking “Close and Apply”, the tool “reduces/cuts my amount of data ”:
- Instead of applying changes made in Power Query to all data; of the almost 3 Million rows; It only loads 50,000 rows (see attached image).
- The transformations I requested in Power Query are performed, but my total Dataset is truncated to only 50,000 rows
Do you know why this happens, and what I can do to fix it? Could it be that my “Native Query” is incompatible with Power Query? Is there any way to apply the extra transformations that I need, using Power Query?
- I attach example of the Query used:
SELECT DISTINCT platform_contact._contoso_company.marketingAutomation.contactIdExt platform_contactid -- Platform Contact ID
,platform_contact.workEmail.address platform_contact_email_address
,platform_contact._contoso_company.marketingAutomation.createdDate platform_contact_createdate-- Platform Contact Create Date
,platform_contact._contoso_company.marketingAutomation.webInteractionDetails.firstWebContactUpdateDate
,platform_contact._contoso_company.marketingAutomation.contactSourceFirst
,platform_contact._contoso_company.marketingAutomation.webInteractionDetails.lastWebContactUpdateDate
,platform_contact._contoso_company.marketingAutomation.webInteractionDetails.lastWebLogonDate
,platform_contact._contoso_company.marketingAutomation.lastLeadMktDetails.stdLeadEligible
,platform_contact._contoso_company.marketingAutomation.extendedWorkDetails.stdPrimaryBusiness
,platform_contact._contoso_company.marketingAutomation.lastLeadMktDetails.funnelLastAQLDate
,platform_contact._contoso_company.marketingAutomation.contactDateTimeLastModified
,platform_contact._contoso_company.marketingAutomation.webInteractionDetails.firstWebContactUpdateSiteLanguage
,platform_contact._contoso_company.marketingAutomation.contactSourceFirstTime
,platform_contact._contoso_company.marketingAutomation.webInteractionDetails.lastWebLogonSiteLanguage
,platform_contact._contoso_company.marketingAutomation.leadExceptionsBitmask1
,platform_contact._contoso_company.marketingAutomation.stdDateLastNormalized
,platform_contact._contoso_company.marketingAutomation.contactMIQ
,platform_contact._contoso_company.marketingAutomation.contactMIQRuleset
FROM platform_contact
,(SELECT platform_contact._contoso_company.marketingAutomation.contactIdExt -- Platform Contact ID
,MAX(platform_contact._contoso_company.marketingAutomation.contactDateTimeLastModified) elqLastRecord
FROM platform_contact
GROUP BY contactIdExt)elqcontact_lastmodified
WHERE platform_contact._contoso_company.marketingAutomation.contactIdExt = elqcontact_lastmodified.contactIdExt
AND platform_contact._contoso_company.marketingAutomation.contactDateTimeLastModified = elqcontact_lastmodified.elqLastRecord
AND platform_contact._contoso_company.marketingAutomation.contactDateTimeLastModified >= TIMESTAMP '2023-01-01 00:00:00'
LIMIT 100000000
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
2 Replies
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,
JingHi 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:
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).
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?
- Scenario:
