Forum Discussion
Issue with Pivot Transformation and Incremental Refresh in Power BI service
Hi everyone,
I'm encountering an issue with the incremental refresh in Power BI when using a pivot transformation in Power Query M. Here's the situation:
I have a dataset where I perform some transformations, and as a final step, I apply a pivot transformation to convert rows from one column into columns. When I enable incremental refresh, the update process fails with the following error message: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"There weren't enough elements in the enumeration to complete the operation."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Detail","detail":{"type":1,"value":"#table({\"Content\", \"Name\", \"Extension\", \"Date accessed\", \"Date modified\", \"Date created\", \"Attributes\", \"Folder Path\"}, {})"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}}],"exceptionCulprit":1}}}
Example power query in M
let
Fonte = Folder.Files("\\10......."),
#"Linhas Filtradas" = Table.SelectRows(Fonte, each [Date modified] >= RangeStart and [Date modified] <= RangeEnd),
#"Arquivos Ocultos Filtrados1" = Table.SelectRows(#"Linhas Filtradas", each [Attributes]?[Hidden]? <> true),
#"Invocar Função Personalizada1" = Table.AddColumn(#"Arquivos Ocultos Filtrados1", "Transformar Arquivo (2)", each #"Transformar Arquivo (2)"([Content])),
#"Colunas Renomeadas1" = Table.RenameColumns(#"Invocar Função Personalizada1", {"Name", "Nome da Origem"}),
#"Outras Colunas Removidas1" = Table.SelectColumns(#"Colunas Renomeadas1", {"Nome da Origem", "Transformar Arquivo (2)"}),
#"Coluna de Tabela Expandida1" = Table.ExpandTableColumn(#"Outras Colunas Removidas1", "Transformar Arquivo (2)", Table.ColumnNames(#"Transformar Arquivo (2)"(#"Arquivo de Amostra (2)"))),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Coluna de Tabela Expandida1",{{"Nome da Origem", type text}, {"Data", type date}, {"praza", Int64.Type}, {"numero_domicilio", Int64.Type}, {"tv_domicilio", Int64.Type}, {"id_parametro", Int64.Type}, {"conteudo_parametro", type text}}),
#"Coluna em pivô" = Table.Pivot(Table.TransformColumnTypes(#"Tipo Alterado", {{"id_parametro", type text}}, "pt-BR"), List.Distinct(Table.TransformColumnTypes(#"Tipo Alterado", {{"id_parametro", type text}}, "pt-BR")[id_parametro]), "id_parametro", "conteudo_parametro")
in
#"Coluna em pivô"
image incremental refresh configuration
Data to import in power bi (Pivot is between id_parametro and conteudo_parametro, being id_parametro transformed in collumns)
Data,praza,number,extension,id_parametro,conteudo_parametro
2024-03-02,11,100002,0,1996,default
2024-03-02,11,100002,0,1997,default
2024-03-02,11,100002,0,1987,0
2024-03-02,11,100002,0,1990,default
2024-03-02,11,100002,0,1977,internet.movistar.com.co
2024-03-02,11,100002,0,1983,1
2024-03-02,11,100002,0,1986,1
2024-03-02,11,100002,0,1991,default
2024-03-02,11,100002,0,1978,default.apn.com.br
2024-03-02,11,100002,0,1981,"12,15"
2024-03-02,11,100002,0,1982,"3,15"
2024-03-02,11,100002,1,1981,"3,15"
2024-03-02,11,100002,1,1982,"3,15"
2024-03-02,11,100002,1,1977,apn.default.com.br
2024-03-02,11,100002,1,1978,default.apn.com.br
2024-03-02,11,100002,1,1997,default
2024-03-02,11,100002,1,1987,0
2024-03-02,11,100002,1,1990,default
2024-03-02,11,100002,1,1991,default
2024-03-02,11,100002,1,1996,default
2024-03-02,11,100002,1,1983,0
2024-03-02,11,100002,1,1986,1
2024-03-02,11,100003,0,1983,1
2024-03-02,11,100003,0,1986,1
2024-03-02,11,100003,0,1996,comcelweb
2024-03-02,11,100003,0,1997,default
2024-03-02,11,100003,0,1978,default.apn.com.br
2024-03-02,11,100003,0,1981,"12,15"
2024-03-02,11,100003,0,1982,"3,15"
2024-03-02,11,100003,0,1977,internet.comcel.com.co
2024-03-02,11,100003,0,1991,default
2024-03-02,11,100003,0,1987,0
2024-03-02,11,100003,0,1990,comcelweb
2024-03-02,11,100003,1,1987,0
2024-03-02,11,100003,1,1990,default
2024-03-02,11,100003,1,1981,"12,15"
2024-03-02,11,100003,1,1982,"3,15"
2024-03-02,11,100003,1,1983,0
2024-03-02,11,100003,1,1986,1
2024-03-02,11,100003,1,1997,default
2024-03-02,11,100003,1,1991,default
2024-03-02,11,100003,1,1996,default
2024-03-02,11,100003,1,1977,internet.movistar.com.co
2024-03-02,11,100003,1,1978,default.apn.com.br
2024-03-02,11,100004,0,1977,m2mco.tigo.com
2024-03-02,11,100004,0,1978,default.apn.com.br
2024-03-02,11,100004,0,1983,1
2024-03-02,11,100004,0,1986,1
2024-03-02,11,100004,0,1987,0
2024-03-02,11,100004,0,1990,default
2024-03-02,11,100004,0,1981,"12,15"
2024-03-02,11,100004,0,1982,"3,15"
2024-03-02,11,100004,0,1991,default
2024-03-02,11,100004,0,1996,default
2024-03-02,11,100004,0,1997,default
The issue happened on the power bi service at the time of update, on desktop updated correctly. My data imported has modified date as currently day.
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.
4 Replies
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.
This error message DM_GWPipeline_Gateway_MashupDataAccessError in Power BI occurs when there is issue with accessing data through the on-premises data gateway. This can be caused diffrent factors, such as network issues, gateway configuration problems, or data source authentication issues.
I don´t believe it´s the problem, because without the refresh incremental enble it works well.
