Em determinados contextos de manipulação de dados, a necessidade de empregar estruturas de loop se faz presente para processar um valor específico ou extrair informações, especialmente quando há relações em múltiplos níveis e a quantidade de níveis não é predefinida. Em ambientes de bancos de dados como SQL Server, Oracle, PostgreSQL, ou outros semelhantes, recorremos a comandos como “while” ou “for” para realizar tais operações. No entanto, quando estamos trabalhando com o Power Query, a abordagem para implementar esses procedimentos difere.
Como podemos realizar essas operações no Power Query?
Vejamos o seguinte exemplo:
Observando a tabela a seguir temos um controle de OS relacionando a Abertura da OS, Complementos dela e o Fechamento.
| Tipo | OS | Data | OS Origem | Valor |
| Abertura | 15640 | 01/01/2024 | 75,00 | |
| Complemento | 15698 | 01/01/2024 | 15640 | |
| Complemento | 15974 | 05/01/2024 | 15698 | |
| Complemento | 16010 | 06/01/2024 | 15974 | |
| Complemento | 16132 | 09/01/2024 | 16010 | |
| Fechamento | 16464 | 12/01/2024 | 16132 | 25,00 |
| Fechamento | 16788 | 15/01/2024 | 16132 | 25,00 |
| Fechamento | 16800 | 16/01/2024 | 15698 | 25,00 |
Suponhamos que precisamos filtra as Aberturas e adicionar a elas o Número do Fechamento, Data e Valor.
Como poderíamos realizar isso?
A lógica é a seguinte
1º Verificar se a OS está atrelada a algum registro.
2º Separar destes os tipos Complemento e Fechamento
3º Se o tipo for igual a Complemento, buscar um fechamento se não retornar o resultado.
Com o conceito definido podemos construir nosso código, abaixo temos um exemplo completo para tratar essa situação.(Criamos uma função personalizada para isso, o bloco da função esta em destaque)
let
Etapa1 = /*Leitura dos dados não tratados*/
Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
/*Função de Loop para encontra os dados do fechamento da OS de Abertura*/
getFechamento = ( pDB, pRelacao) =>
let
Parte1 = /*Verifica se existe alguma OS Relacionada com a OS informada na Função*/
Table.SelectRows(pDB,each [#"OS Origem"] = Int64.From(pRelacao)),
Parte2 = /*Filtra os Complementos encontrados*/
Table.SelectRows(Parte1,each [Tipo] = "Complemento"),
Parte3 = /*Filtra os Fechamentos*/
Table.SelectRows(Parte1,each [Tipo] = "Fechamento"),
RetResultado = /*Avalia para rodar a função novamente criando o processo de Loop*/
if Table.RowCount(Parte2) > 0 /*Verifica se ainda tem registros do tipo Complemento*/
then /*Se existir executa novamente a função*/
Table.Combine({
Table.ExpandTableColumn(
Table.RemoveColumns(
Table.AddColumn( Parte2, "Dados", each @getFechamento(pDB,[OS]), type table)
, {"Tipo", "OS", "Data", "OS Origem", "Valor"})
, "Dados", {"Tipo", "OS", "Data", "OS Origem", "Valor"})
, Parte3 })
else /* Se não existir retorna o resultado*/
Parte3
in
RetResultado,
Etapa2 = /*Filtra os tipos Abertura e chama a Função*/
Table.ExpandTableColumn(
Table.AddColumn(
Table.RemoveColumns(
Table.SelectRows(Etapa1, each [Tipo] = "Abertura")
,{"OS Origem"})
,"Dados", each getFechamento(Etapa1,[OS]), type table)
,"Dados",{"OS","Data","Valor"},{"OS Fechamento","Data Fechamento","Valor Fechamento"}),
Etapa3 = /*Define os tipos aos dados*/
Table.TransformColumnTypes(Etapa2,{{"Tipo", type text}, {"OS", Int64.Type}, {"Data", type date}, {"OS Fechamento", Int64.Type}, {"Data Fechamento", type date},{"Valor", type number}, {"Valor Fechamento", type number} })
in
Etapa3O retorno deste código acima será o seguinte:
| Tipo | OS | Data | Valor | OS Fechamento | Data Fechamento | Valor Fechamento |
| Abertura | 15640 | 01/01/2024 | 75,00 | 16464 | 12/01/2024 | 25,00 |
| Abertura | 15640 | 01/01/2024 | 75,00 | 16788 | 15/01/2024 | 25,00 |
| Abertura | 15640 | 01/01/2024 | 75,00 | 16800 | 16/01/2024 | 25,00 |
Ao analisar o segmento da nossa função personalizada, percebemos que a invocamos dentro do próprio corpo da função, é ai que está o pulo do gato para criar um Loop no Power Query. Sempre que há a necessidade de autorreferenciar uma função precisamos anteceder o seu nome com o caractere @. Isso notifica ao compilador que estamos fazendo referência à própria função durante a chamada, estabelecendo assim a lógica essencial para o funcionamento do loop.
/*Função de Loop para encontra os dados do fechamento da OS de Abertura*/
getFechamento = ( pDB, pRelacao) =>
let
Parte1 = /*Verifica se existe alguma OS Relacionada com a OS informada na Função*/
Table.SelectRows(pDB,each [#"OS Origem"] = Int64.From(pRelacao)),
Parte2 = /*Filtra os Complementos encontrados*/
Table.SelectRows(Parte1,each [Tipo] = "Complemento"),
Parte3 = /*Filtra os Fechamentos*/
Table.SelectRows(Parte1,each [Tipo] = "Fechamento"),
RetResultado = /*Avalia para rodar a função novamente criando o processo de Loop*/
if Table.RowCount(Parte2) > 0 /*Verifica se ainda tem registros do tipo Complemento*/
then /*Se existir executa novamente a função*/
Table.Combine({
Table.ExpandTableColumn(
Table.RemoveColumns(
Table.AddColumn( Parte2, "Dados", each @getFechamento(pDB,[OS]), type table)
, {"Tipo", "OS", "Data", "OS Origem", "Valor"})
, "Dados", {"Tipo", "OS", "Data", "OS Origem", "Valor"})
, Parte3 })
else /* Se não existir retorna o resultado*/
Parte3
in
RetResultado,Ao utilizar loops no tratamento de dados com o Power Query ou qualquer outra ferramenta, é importante considerar alguns cuidados para garantir a eficiência e a correta manipulação dos dados.
Evite o uso de loops desnecessários ou excessivamente longos, pois estes impactaram negativamente o desempenho da consulta.
Ao usar loops recursivos, certifique-se de que a condição de parada seja clara e alcançável para que não ocorra um loop infinito e assim travar a consulta.
Depois nos conta ai se te ajudou o se encontrou uma abordagem melhor.
Desejamos que o conteúdo tenha sido útil para o seu aprendizado.
– essa foi mais uma dica do Ornit 😉.
Gosta do nosso projeto, colabore se puder PIX euapoio@ornit.com.br
Deixe um comentário