Layouts de calendário para formato tabular usando Power Query

Neste tutorial, você aprenderá como transformar vários layouts de tipo de calendário em um formato tabular usando o Power Query no LuckyTemplates. Você aprenderá como transformar vários arquivos em vez de um único arquivo de uma pasta. Você pode assistir ao vídeo completo deste tutorial na parte inferior deste blog.

Este tutorial também ensinará como preencher lacunas em seus dados e como colhê-los no LuckyTemplates.

Índice

• Preparando os arquivos e layouts no Power Query
• Formulando cálculos no Power Query
• Preenchendo as lacunas nas datas
• Agrupando Tabelas Separadas
• Conclusão

Preparando os arquivos e layouts no Power Query

Este é o layout típico dos arquivos e semanas dispostos horizontal e verticalmente. De arquivo para arquivo, o número de linhas pode diferir devido à variação no número de linhas.

Agora, vá para o Power Query e obtenha os arquivos. Selecione Nova fonte e clique em Mais. Em seguida, clique em Todos e escolha Pasta.

Selecione Parâmetro e pressione OK.

Ele vai mostrar os arquivos em Pasta. Você precisa reunir todos os arquivos em uma única consulta de resultado. Clicar em Combinar e transformar carrega os dados e cria várias consultas.

Em seguida, escolha qualquer arquivo na pasta como um arquivo de amostra e selecione qualquer item que deseja iniciar.

Olhando para Query1, você pode ver os dados de File1 e File2.

Formulando cálculos no Power Query

O formato dos dados na Consulta1 ainda está incorreto. Você pode alterá-lo por meio da consulta Transform Sample File.

Se você alterar o formato dessa consulta para uma forma tabular, ela servirá como um modelo para todos os arquivos da pasta.

Remova as primeiras 2 linhas e Column1.

Em seguida, preencha as lacunas dentro da tabela. Vá para a guia Transformar e selecione Transpor. Selecione as colunas que contêm datas e clique no cabeçalho. Clique em Preencher e escolha Abaixo.

As colunas com datas na barra de fórmulas significam que, se houver um número variável de linhas em cada semana, os resultados não estarão nessas colunas. Portanto, exclua a etapa Filled Down na seção Applied Steps.

Cada grupo de tabelas começa com uma coluna contendo datas. Você precisa executar operações básicas para recuperá-los em um formato tabular.

Adicione uma etapa manual pressionando f(x) na barra de fórmulas. Em seguida, insira Table.ToColumns . Isso transforma cada coluna em uma lista contendo listas.

Transforme a lista em uma tabela e adicione um número de índice. Você pode mudar de 0 ou 1.

Preenchendo as lacunas nas datas

Ainda existem lacunas nas datas que você precisa preencher. No entanto, a operação de preenchimento não funciona para listas. Você precisa transformar esta lista em uma tabela.

Selecione a coluna Índice e clique em Agrupar por. Substitua Count por ToTable para alterar o nome da coluna. Altere RowCount para FromColumns e aponte para Column1 onde as listas são colocadas. Em seguida, transforme-o em uma tabela de tipos.

Agora você pode ver que a lista se transformou em uma tabela com apenas uma coluna.

Agora, execute a operação de preenchimento para cada tabela. Primeiro, adicione uma coluna personalizada e chame-a de FillDown .

Você precisa acessar o primeiro valor da primeira coluna da tabela, portanto, para a fórmula, use IF . Faça referência à coluna ToTable que contém a tabela. Insira Column1 como a coluna que você deseja referenciar. Em seguida, use null entre eles para recuperar o primeiro valor da lista.

Em seguida, verifique se isso é uma data. Se for, preencha essa coluna com Table.Filldown. Dentro dessa função, insira o ToTable e Column1. Mas se não for uma data, insira ToTable para retornar a tabela inteira.

Isso lhe dará uma nova coluna com tabelas preenchidas.

Descubra onde as datas da coluna são colocadas e divida-as em tabelas separadas. Para fazer isso, você deve transformar as tabelas em uma lista de colunas.

Após as funções Table.FillDown e Else na barra de fórmulas, faça referência a Column1.

Isso transformará suas tabelas em listas com conjuntos de datas.

Em seguida, transforme a lista de volta em uma tabela atualizando sua coluna de índice. Lembre-se de que o local de divisão é onde está a posição da coluna de data. Portanto, adicione uma coluna personalizada e chame-a de Index2 . Em seguida, verifique se a lista contém datas.

Use IF e insira FillDown. Você só precisa verificar o primeiro valor da lista. Se for uma data, retorne Index. Caso contrário, insira null . Em seguida, preencha essa coluna.

Agrupando Tabelas Separadas

Use Group by na coluna Index2. Isso agrupa as tabelas com os mesmos valores de Index2 em uma.

Você não vai contar as linhas. Portanto, altere RowCount para FromColumns e adicione a coluna FillDown. Em seguida, altere o tipo para tabela.

Se você clicar em uma tabela, verá as colunas dentro dela.

Em seguida, você deve anexar todas as tabelas separadas em uma única tabela. Crie uma etapa manual e use Table.Combine. Em seguida, faça referência à coluna Count dentro dela.

Selecione Coluna1 e Coluna2. Clique em Transform e selecione Unpivot Other Columns. Em seguida, ele mostrará 4 colunas.

Remova a coluna Attribute porque ela não é necessária. Em seguida, altere os nomes das colunas para Date , Movements e Name . Altere o tipo de dados da coluna Data para Data e a coluna Nome para Texto.

Agora você atualizou as transformações para o arquivo de exemplo de transformação.

Se você for para a Consulta1, verá que a Coluna1 da tabela não pode ser encontrada. Portanto, remova a etapa Changed Type na seção Applied Steps.

Por fim, altere os tipos de dados das colunas. Defina o tipo de dados da coluna Data como Data. Em seguida, defina o tipo de dados das colunas Movimentos e Nome como Texto.

Agora você atualizou e alterou o formato do modelo de dados para um formato tabular.

Calendários personalizados do LuckyTemplates: Calculando alterações mensais – 445 calendários
Técnicas de formatação condicional personalizada no LuckyTemplates
Como trabalhar com várias datas no LuckyTemplates

Conclusão

Em seu relatório de dados, você precisa preencher as lacunas em sua tabela de datas transformando os layouts de calendário em um formato tabular.

Você pode transformar várias colunas usando o Power Query no LuckyTemplates. Isso dará ao seu relatório informações corretas e valiosas para colher.

Tudo de bom,

melissa