LuckyTemplates Power Query: removendo linhas duplicadas

Neste blog, mostrarei como remover linhas duplicadas no LuckyTemplates Power Query com base nas condições. Você pode assistir ao vídeo completo deste tutorial na parte inferior deste blog.

Este exemplo é de uma pergunta que foi levantada no . O membro tem dados transacionais e deseja remover linhas que se anulam.

Existem dois tipos transacionais distintos, um para entrada e outro para saída. Ela também adicionou contagem de movimento. E com base nisso, ela formulou três cenários.

O cenário um é baseado em duas linhas. Se eles se cancelam, ambas as linhas devem ser removidas. Se não for esse o caso, ambas as linhas devem ser mantidas.

Os cenários dois e três são baseados em uma combinação de linhas, em parte cancelando uma à outra. Essas linhas devem ser removidas primeiro a entrar, primeiro a sair, para que apenas as últimas transações recebidas sejam mantidas.

Basicamente, estamos repetindo a mesma lógica várias vezes. Devemos ser capazes de projetar uma única abordagem para atender a todos esses cenários.

Algumas das coisas que vamos fazer são adicionar uma coluna com quantidades absolutas e agrupar por “adicionar uma coluna de saldo de quantidade” e uma tabela aninhada usando a opção “todas as linhas”. Também transformaremos essas tabelas aninhadas, classificaremos o tipo de movimento e a data de lançamento, criaremos um total acumulado e manteremos apenas as linhas com base em uma condição.

Eu classifiquei isso em um nível intermediário porque a maioria das transformações que podemos fazer aqui é usando a interface do usuário. Dito isso, vamos ao Power Query do LuckyTemplates.

Índice

• Adicionando coluna com quantidades absolutas no Power Query
• Agrupar por índice de ID e quantidade absoluta
• Transformando tabelas aninhadas no LuckyTemplates Power Query
  • Classificando o tipo de movimento e a data de lançamento
  • Criando um total corrente
  • Filtrando linhas com base em uma condição
• Conclusão

Adicionando coluna com quantidades absolutas no Power Query

Começaremos adicionando uma coluna com quantidades absolutas. Selecione a coluna Quantidade e , em seguida, na faixa ou guia Adicionar coluna , vá para Científico e clique em Valor absoluto .

Vou renomear esta coluna na barra de fórmulas.

Agrupar por índice de ID e quantidade absoluta

Agora podemos agrupar por Índice de ID e Quantidade Absoluta. Selecione ID Index , mantenha pressionada a tecla shift ou control para fazer uma seleção múltipla e clique em Abs qty (quantidade absoluta).

Você encontrará Agrupar por na guia Transformar.

Mas você também pode encontrá-lo na guia Início.

Clique nele e agora vamos adicionar uma nova coluna. Não é uma “contagem”, mas vai ser a “soma” da quantidade. Então, vamos renomeá-lo para Qty (quantity) Balance . Será uma soma da coluna de quantidade. Em seguida, adicionaremos outra agregação, mas será especial, então selecionaremos All Rows . Vamos também dar um nome a esta coluna ( AllRows ). Em seguida, pressione OK.

Se clicarmos no espaço em branco da tabela, veremos uma visualização da tabela aninhada abaixo. Vemos que as quantidades dessa tabela aninhada se anulam e o saldo da quantidade é zero.

Se olharmos para a próxima tabela aninhada (clique para o lado no espaço em branco da próxima linha), veremos que a coluna de quantidade não se cancela e um saldo de quantidade é maior que zero.

Vamos adicionar uma coluna personalizada para que possamos usá-la para nossas transformações de tabelas aninhadas posteriormente. Então, selecione Adicionar coluna personalizada .

Vamos renomear essa coluna para Transformações e adicionaremos zero (0) como espaço reservado. Em seguida, clique em OK.

Transformando tabelas aninhadas no LuckyTemplates Power Query

Agora estamos prontos para criar a lógica para transformar essas tabelas aninhadas. Mas não queremos escrever um monte deles e não precisamos.

Podemos usar a interface do usuário na maior parte. Se construirmos a lógica para essas transformações em uma consulta separada, o cenário mais elaborado que tivemos foi aquele com três ou quatro linhas.

Agora, se eu clicar do lado direito na última linha, vejo que esta tabela tem quatro linhas. Então, podemos usar isso para construir nossas transformações.

Clique com o botão direito do mouse na lateral do espaço em branco e selecione Adicionar como nova consulta .

Vemos que a tabela aninhada foi expandida e adicionada como uma nova consulta e podemos usar isso para criar a lógica de que precisamos.

Classificando o tipo de movimento e a data de lançamento

Agora, a primeira coisa que precisamos fazer é classificar em tipo de movimento . Vamos escolher descendente . Desta forma, as transações de saída estarão sempre no topo.

A seguir, classificaremos a Data de Lançamento como crescente , certificando-se de que, se omitirmos linhas, sempre será feito de acordo com o princípio FIFO (primeiro a entrar, primeiro a sair).

Criando um total corrente

Para identificar as linhas que vamos remover, vou adicionar um total em execução para desabilitar e podemos usar um List.FirstN para essa tarefa. Esta função cria uma lista com base em outra lista, onde os itens principais são mantidos com base em um número específico ou em um critério.

Voltemos à nossa consulta. Precisamos de duas coisas para List.FirstN. Primeiro, precisamos dessa lista, e essa lista é nossa coluna Quantidade . Se eu clicar com o botão direito do mouse no cabeçalho da coluna Quantity, teremos a opção de Add as NewQuery .

E aqui vemos o código que precisamos para gerar essa lista. Na etapa aplicada, você pode ver que ela aponta para a última etapa e identifica a coluna nesses colchetes.

A segunda parte que precisamos para List.FirstN é um número para identificar quantos números manter dessa lista. Podemos usar o índice para isso, então vou adicionar uma coluna de índice de um (1) para esta linha.

Portanto, para o número 1, ele manterá o número da primeira linha da coluna Quantidade e assim por diante. E agora podemos resumir isso.

Vamos adicionar uma coluna personalizada e chamá-la de Running Total . Usamos List.FirstN aqui e podemos ver que o primeiro parâmetro foi essa lista, a coluna Quantity. Precisamos apontar para a última etapa, e essa última etapa é o Índice adicionado .

Identificamos a coluna que queremos, e essa é a nossa coluna Quantidade. Nossa contagem está na coluna Índice.

Agora ele retorna uma lista. Se clicarmos ao lado no espaço em branco, podemos ver o conteúdo dessa lista. Para o primeiro registro, ele manteve apenas a linha superior dessa lista. Para o segundo registro, manteve as duas primeiras linhas.

Agora tudo o que precisamos fazer é somar essas quantidades e podemos usar Lists.Sum para fazer isso. Vou adicionar isso na barra de fórmulas. E também vou colocar Type .

Filtrando linhas com base em uma condição

Com esse total acumulado, podemos identificar as linhas que queremos manter. Queremos apenas manter as linhas maiores que zero, então vamos adicionar uma condição de filtro.

Agora podemos remover nossas colunas auxiliares. Selecione Índice e Total acumulado e clique em Remover colunas.

No Editor Avançado, agora podemos copiar o código que criamos. Abro o Advanced Editor , e podemos ver que esta é a nossa etapa dividida. Então, podemos selecionar e copiar tudo abaixo disso.

Agora vamos voltar à nossa consulta original. Novamente, abra o Editor Avançado. E em vez do espaço reservado, vamos para uma nova linha. Vou usar a expressão “ let ” porque ela pode capturar valores de cálculos intermediários em variáveis. Então, “deixe” e depois vá para uma nova linha e cole minhas transformações. Também vou declarar Type .

O código destacado acima aponta para nossa tabela aninhada expandida, que é colocada na coluna Todas as linhas. Então, vou apontar para isso, substituindo este código destacado por AllRows .

Portanto, nossa coluna de transformação agora possui tabelas aninhadas. Construímos nossa consulta com base na última linha, certo? E isso continha quatro linhas quando começamos e continha apenas duas. Também temos aquelas quantidades balanceadas com zero, certo? E agora tem uma mesa vazia.

Podemos removê-los filtrando na coluna Saldo de quantidade. Não queremos que o saldo da quantidade seja igual a zero.

E então, vou selecionar minha coluna de transformação e remover outras colunas. Agora posso expandir minhas tabelas aninhadas de transformação. Não use o nome da coluna original como prefixo e pressione OK.

Na guia Transform, selecione Data Type e pronto. E é assim que você limpa os dados transacionais.

Fundamentos não dinâmicos e dinâmicos no LuckyTemplates – Revisão do Editor de consultas
Minhas dicas práticas ao usar o editor avançado do LuckyTemplates
Transformações avançadas no LuckyTemplates

Conclusão

Neste tutorial, mostrei como limpar dados transacionais, removendo especificamente linhas duplicadas. Essa é uma ótima técnica que você pode usar ao utilizar o LuckyTemplates Power Query.

Eu espero que você tenha gostado deste. Assista ao tutorial em vídeo completo abaixo para obter mais detalhes. Confira também os links abaixo para obter mais conteúdo relacionado ao LuckyTemplates Power Query Editor.

Saúde!

melissa