Transformando layout de tipo de calendário em formato tabular no LuckyTemplates usando o Query Editor

Analisar os dados que temos pode ser difícil se não estiverem organizados corretamente. Neste tutorial, explicarei como transformar dados repetidos verticais e horizontais em um formato tabular. Fazemos isso para facilitar a análise usando o LuckyTemplates. Você pode assistir ao vídeo completo deste tutorial na parte inferior deste blog.

Este tutorial foi feito para resolver a questão de um  membro  . O membro solicitou uma maneira de remodelar seus dados de um layout do tipo calendário para um formato tabular.

Antes de começarmos, vamos primeiro examinar os dados que temos.

Aqui, temos um layout de dados em que os dias são configurados horizontalmente e as semanas são configuradas verticalmente. Esse tipo de arranjo é familiar para a maioria de nós, pois é assim que podemos entender facilmente os dados.

No entanto, esse tipo de layout é mais difícil de analisar, pois há muitas colunas e linhas a serem consideradas.

Dito isso, vamos transformar os dados em um formato de tabela adequado onde terminamos com uma coluna para datas, uma coluna para os movimentos e outra coluna para os nomes.

Índice

• Limpando dados para um formato tabular adequado
• Quebrando as Mesas
• Identificando a lista para as novas tabelas para um formato tabular adequado
• Determinando quais valores pertencem à tabela
• Agrupando os dados para um formato tabular adequado
• Anexando as tabelas separadas
• Conclusão

Limpando dados para um formato tabular adequado

Para facilitar, vamos transformar nossos dados dentro do Power Query Editor . Mas antes de fazermos isso, vamos nos certificar de que a barra de fórmulas esteja visível em sua tela. Se não estiver, vá para a guia Exibir e marque a caixa ao lado da barra de fórmulas.

O primeiro passo é remover os valores desnecessários em nossos dados. Vamos remover a primeira coluna e as duas primeiras linhas de nossos dados, pois realmente não precisamos delas. Para fazer isso, clique na guia Remover Linhas, clique em Remover Linhas Superiores, digite 2 e pressione OK.

E agora, para remover a primeira coluna, vá para a guia Escolher colunas e desmarque a coluna 1 desmarcando a caixa ao lado dela.

Vamos também remover os índices de linhas 14 a 16, pois eles estão completamente preenchidos apenas com valores nulos. Novamente, vá para a guia Remover Linhas e clique em Remover Linhas em Branco .

Outro problema que temos é que os valores de data sempre ignoram uma coluna. As datas são sempre seguidas por um valor nulo conforme destacado abaixo. Isso acontece não apenas na linha superior, mas também nas linhas abaixo.

Para preencher essas lacunas, vamos primeiro transpor nossa tabela de dados indo até a  guia Transformar e clicando no botão Transpor.

A mesa agora deve parecer que foi virada de lado após a execução da ação.

Em seguida, selecione todas as colunas que contêm as datas, clique com o botão direito do mouse no cabeçalho da coluna e selecione Preencher.

Isso deve duplicar as datas e preencher as lacunas nas colunas selecionadas.

Quebrando as Mesas

Olhando para os dados que temos agora, vemos que existem quatro tabelas adjacentes umas às outras. As colunas 1 a 7 formam uma tabela, as colunas 8 a 13 formam outra tabela e assim por diante.

Para anexá-los em uma única tabela, precisamos executar uma operação não dinâmica básica para transformá-la em um formato tabular adequado. Mas como as tabelas são adjacentes umas às outras, não podemos simplesmente fazer isso. Temos que separar esta mesa e juntá-la novamente em mesas menores.

Para fazer isso, criaremos a primeira fórmula deste tutorial. Vamos clicar no botão fx e adicionar a fórmula que vamos usar. Vamos usar a operação Table.ToColumns . Isso cria uma lista de listas aninhadas de valores de coluna de uma tabela. Cada coluna será transformada em uma lista.

Foi assim que montei a fórmula.

A fórmula cria uma lista das colunas que temos anteriormente. O primeiro valor da lista contém a coluna 1, o segundo valor contém a coluna 2 e assim por diante.

Identificando a lista para as novas tabelas para um formato tabular adequado

Agora, devemos identificar quais valores na lista contêm valores de data. Uma vez que sabemos onde essas datas estão localizadas, podemos determinar onde cada uma de nossas tabelas separadas começa ou termina.

Para fazer isso, vamos primeiro converter a lista de volta em uma tabela clicando no botão To Table no canto superior esquerdo.

Em seguida, adicione uma coluna de índice ao lado dela.

A próxima coisa que faremos é adicionar outra coluna ao lado da coluna de índice. Essa coluna deve nos ajudar a determinar quais valores na Coluna1 contêm os valores de data. Vamos nomear esta coluna Index2.

Para fazer isso, vamos incorporar uma fórmula no Index2, que retorna o valor do índice da Column1 se contiver uma data.

Vamos usar a e, em seguida, referenciar a Coluna1. Os colchetes servirão como o operador; adicionar 0 entre esses colchetes significa que obtemos o primeiro valor da lista; então determinamos se é uma data. Se for, quero que a fórmula retorne o número do índice; se não for, não quero que retorne nenhum valor.

Como o primeiro valor em Column1 contém uma data, o primeiro valor em Index2 deve conter seu número de índice 0. Além disso, como o segundo valor em Column1 não contém uma data, o segundo valor em Index2 deve conter apenas um valor nulo.

Foi assim que configurei a fórmula para Index2. Apenas certifique-se de que não haja erros de sintaxe em sua fórmula.

Depois de fazer isso, nossa coluna Index2 agora deve ter o índice dos valores na Column1 que contém a data. Ele também incluirá alguns valores nulos como resultado da fórmula que configuramos.

Determinando quais valores pertencem à tabela

Agora, esses valores de índice dentro de Index2 indicam o início e o fim das tabelas separadas que temos. Ou seja, 0 marca o início da primeira tabela, 7 marca o final da primeira tabela e o início da segunda tabela e assim por diante.

Para nos ajudar a identificar quais valores na Coluna1 pertencem à mesma tabela, vamos aplicar uma ação de preenchimento em nossa nova coluna.

Depois de executar a ação, os valores nulos agora devem ser substituídos por zeros, setes, treze e vinte. Esses números indicam que pertencem à mesma tabela. Ou seja, os zeros formam uma tabela, os setes formam outra tabela e assim por diante.

Agrupando os dados para um formato tabular adequado

Como já sabemos quais valores na Coluna1 pertencem à mesma tabela, vamos agora agrupá-los. Para isso, usaremos a operação Group By.

Ao fazer isso, acabamos com apenas 4 valores em nossa coluna Index2. Observe que outra coluna chamada Count está ao lado de nossa coluna Index2. Esta coluna conta o número de linhas em cada um dos nossos valores na coluna Index2.

No entanto, isso não é realmente o que queremos fazer. O que precisamos fazer é formar uma nova tabela usando as colunas que temos.

Para fazer isso, vamos usar a operação Table.FromColumns , referenciar a coluna que contém nossos valores de lista que é Column1 e, em seguida, alterar o tipo de retorno para uma tabela.

É importante garantir que referenciamos a coluna correta em nossa fórmula. Verifique se a Coluna1 realmente contém os valores das listas.

Isso une todos os valores na Coluna1 com um valor de Index2 de 0 em uma tabela, aqueles com o valor de Index2 de 7 em outra tabela e assim por diante.

Verificamos cada registro para o valor 0 e, em seguida, recuperamos o valor da lista da Coluna1. Por fim, juntamos essas listas para formar uma única tabela usando esse código M.

Pegamos as primeiras 7 colunas e as costuramos em uma única tabela, pegamos as próximas colunas, as costuramos em outra tabela e assim por diante.

Anexando as tabelas separadas

Agora que já as agrupamos, vamos tentar anexar todas as tabelas adicionando outra etapa. Novamente, vamos pressionar o botão fx para adicionar uma nova fórmula.

A operação Table.Combine mescla uma lista de tabelas e, a seguir, cria uma única tabela a partir delas. Aqui, vamos referenciar a coluna que contém nossa lista de tabelas, que é a coluna Count.

Agora temos uma tabela onde todas as tabelas aninhadas são anexadas de volta em uma única grande tabela.

Depois de anexar todas as tabelas, agora podemos executar as últimas etapas para transformar nossos dados em um formato tabular adequado. Selecione as duas primeiras colunas de sua tabela e, em seguida, clique no botão Unpivot Other Columns na guia Transform.

Depois disso, remova a coluna de atributo, pois realmente não precisamos dela. Vá para Escolher colunas e desmarque a coluna de atributos.

Finalmente, defina os tipos corretos de todas as colunas e rotule-as adequadamente.

Clique duas vezes no cabeçalho de cada coluna para alterar seu nome de acordo.

Para a primeira coluna, altere seu tipo para data, pois contém nossos valores de data.

Em seguida, selecione a segunda e a terceira coluna e defina seus tipos como texto.

Tudo feito! É assim que nosso formato tabular adequado deve ser.

Conclusão

Neste tutorial, transformamos alguns dados, que foram apresentados em um layout do tipo calendário, em um formato tabular adequado. Primeiro limpamos nossos dados, os dividimos em tabelas menores, agrupamos as tabelas que pertencem umas às outras e, em seguida, anexamos todas elas no final.

Este tutorial oferece muito conhecimento sobre como modelar adequadamente seus dados para uma análise mais fácil.

Compreender os conceitos que foram usados ​​aqui é muito importante, pois eles também podem ser aplicados de várias outras maneiras.

Se os conceitos ainda não estão claros para você, verificar os links abaixo pode ajudá-lo.

Divirta-se trabalhando com este!

melissa