Error on refresh dataset online

[gregoliveira]

Hi.

I have a query that's working perfectly on Power BI Desktop.

let
Origem = Table.FromList(
{"state=10",
"state!=10^u_planned_start_date>"& dataInicio{0}[Value] &"^u_planned_start_date<"& dataFim{0}[Value],
"state!=10^u_planned_end_date>"& dataInicio{0}[Value] &"^u_planned_end_date<"& dataFim{0}[Value]
},
null,
{"parametrosConsulta"}
),
#"Consultando intervencoesSGO" = Table.AddColumn(Origem, "intervencoesSGO", each consultaSGO("now/table/x_petro_gas_energy_operational_change", [parametrosConsulta], 0)),
#"Expandindo intervencoesSGO 1" = Table.ExpandListColumn(#"Consultando intervencoesSGO", "intervencoesSGO"),
#"Expandindo intervencoesSGO 2" = Table.ExpandRecordColumn(#"Expandindo intervencoesSGO 1", "intervencoesSGO", {"state", "u_notify_ons", "u_emergency_return", "change_code", "occurrence_comment", "u_planned_start_date", "u_return_time", "closed_at", "u_change_type", "opened_at", "description", "sys_id", "u_planned_end_date"}, {"state", "u_notify_ons", "u_emergency_return", "change_code", "occurrence_comment", "u_planned_start_date", "u_return_time", "closed_at", "u_change_type", "opened_at", "description", "sys_id", "u_planned_end_date"}),
// Usado campo sys_id (futuro sys_id_intervecao)
#"Removendo Intervenções Duplicatas" = Table.Distinct(#"Expandindo intervencoesSGO 2", {"sys_id"}),
#"Removendo Colunas Processamento 1" = Table.RemoveColumns(#"Removendo Intervenções Duplicatas",{"parametrosConsulta"}),
#"Colunas Renomeadas 1" = Table.RenameColumns(#"Removendo Colunas Processamento 1",{{"sys_id", "sys_id_intervencao"}}),
#"Colunas Renomeadas 2" = Table.RenameColumns(#"Colunas Renomeadas 1",{{"state", "state_intervecao"}}),
// URL para consultar a intervenção diretamente no SGO
#"Coluna urlIntervencao" = Table.AddColumn(#"Colunas Renomeadas 2", "urlIntervencao", each "https://petrobras.service-now.com/now/nav/ui/classic/params/target/x_petro_gas_energy_operational_ch..." & [change_code] & "%26sysparm_first_row%3D1%26sysparm_view%3D"),
#"Colunas Reordenadas" = Table.ReorderColumns(#"Coluna urlIntervencao",{"sys_id_intervencao", "change_code", "u_change_type", "state_intervecao", "u_planned_start_date", "u_planned_end_date", "u_notify_ons", "u_emergency_return", "u_return_time", "occurrence_comment", "opened_at", "closed_at", "description", "urlIntervencao"}),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Colunas Reordenadas",{{"sys_id_intervencao", type text}, {"change_code", type text}, {"u_change_type", type text}, {"state_intervecao", type text}, {"u_planned_start_date", type datetime}, {"u_planned_end_date", type datetime}, {"u_notify_ons", type text}, {"u_emergency_return", type text}, {"u_return_time", type datetime}}),
#"Horário Extraído" = Table.TransformColumns(#"Tipo Alterado",{{"u_return_time", DateTime.Time, type time}}),
#"Tipo Alterado1" = Table.TransformColumnTypes(#"Horário Extraído",{{"occurrence_comment", type text}, {"opened_at", type datetime}, {"closed_at", type datetime}, {"description", type text}, {"urlIntervencao", type text}}),
#"Valor Substituído Falha" = Table.ReplaceValue(#"Tipo Alterado1","forced","Falha",Replacer.ReplaceText,{"u_change_type"}),
#"Valor Substituído Programa" = Table.ReplaceValue(#"Valor Substituído Falha","planned","Programada",Replacer.ReplaceText,{"u_change_type"}),
#"Valor Substituído Retorno Não permitido" = Table.ReplaceValue(#"Valor Substituído Programa","not_allowed","Não permitido",Replacer.ReplaceText,{"u_emergency_return"}),
#"Valor Substituído Retorno Imediato" = Table.ReplaceValue(#"Valor Substituído Retorno Não permitido","immediate","Imediato",Replacer.ReplaceText,{"u_emergency_return"}),
#"Valor Substituído Envio ONS sim" = Table.ReplaceValue(#"Valor Substituído Retorno Imediato","yes","Sim",Replacer.ReplaceText,{"u_notify_ons"}),
#"Valor Substituído Envio ONS não" = Table.ReplaceValue(#"Valor Substituído Envio ONS sim","no","Não",Replacer.ReplaceText,{"u_notify_ons"}),
#"Ajustado fuso-horárcio brasileiro" = Table.TransformColumns(#"Valor Substituído Envio ONS não",{{"u_planned_start_date", each (_ - #duration(0,3,0,0)), type datetime}, {"u_planned_end_date", each (_ - #duration(0,3,0,0)), type datetime}}),
#"Coluna dataInicio" = Table.AddColumn(#"Ajustado fuso-horárcio brasileiro", "dataInicio", each DateTime.Date([u_planned_start_date]), type date),
#"Coluna dataFim" = Table.AddColumn(#"Coluna dataInicio", "dataFim", each DateTime.Date([u_planned_end_date]), type date),
#"Substituindo data null hoje" = Table.ReplaceValue(#"Coluna dataFim",null, DateTime.Date(DateTime.FixedLocalNow()),Replacer.ReplaceValue,{"dataInicio", "dataFim"}),
#"Add coluna pendente" = Table.AddColumn(#"Substituindo data null hoje", "pendente", each ([state_intervecao] = "3") or ([state_intervecao] = "5") or (([u_planned_start_date] < fSgiDataAtualiza{0}[Value]) and ([state_intervecao] = "9")) or (([u_planned_end_date] < fSgiDataAtualiza{0}[Value]) and ([state_intervecao] = "10"))),
#"Tipo coluna pendente" = Table.TransformColumnTypes(#"Add coluna pendente",{{"pendente", type logical}})
in
#"Tipo coluna pendente"

Whenever I try to refresh my report on Power BI Online, I get an error message and it doesn't work properly. The error message is:

[Unable to combine data] Section1/fSgoIntervencoes-Documentos/Tipo coluna pendente references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action.

What's peculiar is that when I remove the last two steps of my process, the dataset refreshes without any issues. Can you help me understand the cause of this behavior?

[v-xiaoyan-msft]

Hi @gregoliveira ,

This can have several problems behind, check the documentation below with information about possible errors and examples:

Dealing with errors - Power Query | Microsoft Learn

Behind the scenes of the Data Privacy Firewall - Power Query | Microsoft Learn

Best regards.
Community Support Team_ Caitlyn

[gregoliveira]

Hi, @v-xiaoyan-msft !

This is the point: the query does not show any error to me. When I refresh it on PBI Online, it shows this error. If I remove the column pointed out in the error message, the dataset refreshes perfectly.