Forum Discussion
Issue with Pivot Transformation and Incremental Refresh in Power BI service
Thank you for your help.
I corrected the parameter RangeEnd as you mentioned. I solved the issue by performing the pivot transformation on my CSV files using Python before continuing with Power Query. I realized that when we perform a pivot transformation in Power Query, the incremental refresh doesn't work.
I made the following observations:
Initial Query without Pivot Transformation:
- I configured incremental refresh on a query named APN without applying a pivot transformation.
- I then created a referenced query from APN and applied the pivot transformation in this referenced query.
- The referenced query did not refresh incrementally, but APN did.
Pivot Transformation in the Initial Query:
- I tried to configure incremental refresh directly in the APN query, including the pivot transformation.
- This resulted in errors in the Power BI Service, indicating that a column could not be found.
Multiple issues.
- you don't usually pivot in Power Query. Is your sample data pre or post pivot?
#"Linhas Filtradas" = Table.SelectRows(Fonte, each [Date modified] >= RangeStart and [Date modified] <= RangeEnd),
That is incorrect. One of the parameters must be inclusive, the other must be exclusive. Change to
#"Linhas Filtradas" = Table.SelectRows(Fonte, each [Date modified] >= RangeStart and [Date modified] < RangeEnd),
- [Date Modified] and Incremental refresh do not go well together. Unless you refresh (many) more partitions you will risk data duplication across partitions. Use [Date Created] or another immutable date.
Thank you for your help.
I corrected the parameter RangeEnd as you mentioned. I solved the issue by performing the pivot transformation on my CSV files using Python before continuing with Power Query. I realized that when we perform a pivot transformation in Power Query, the incremental refresh doesn't work.
I made the following observations:
Initial Query without Pivot Transformation:
- I configured incremental refresh on a query named APN without applying a pivot transformation.
- I then created a referenced query from APN and applied the pivot transformation in this referenced query.
- The referenced query did not refresh incrementally, but APN did.
Pivot Transformation in the Initial Query:
- I tried to configure incremental refresh directly in the APN query, including the pivot transformation.
- This resulted in errors in the Power BI Service, indicating that a column could not be found.