Criando valores para cada data usando a fórmula do Power Query

Neste tutorial, você aprenderá como criar valores para cada dia usando a fórmula do Power Query no LuckyTemplates.

A criação de valores para datas é uma técnica importante a ser aprendida no LuckyTemplates. Ele pode fornecer os dados que você deseja ver ao longo dos dias em seu modelo. Esta técnica também cobre as lacunas entre as colunas.

Esta é uma ótima ferramenta para usar quando faltam informações em suas tabelas de datas.

Aqui está um guia passo a passo sobre como fazer isso: Você pode assistir ao vídeo completo deste tutorial no final deste blog.

Índice

• Definir valores adicionais
• Crie a condição de filtro para os IDs
• Adicione a condição de filtro para as datas
• Verifique e classifique a coluna de data
• Use a função Table.FirstN
• Obter a diferença de dia entre 2 valores de data
• Determinar uma etapa de peso
• Preencha o espaço
• Obtenha a projeção de peso das datas
• Conclusão

Definir valores adicionais

Para conjuntos de dados contendo IDs, Datas e Pesos, você precisa preencher a lacuna entre essas medidas. Isso pode ser feito criando um intervalo de datas para distribuir a diferença ao longo dos dias.

Para fazer isso, vá para Power Query para ver sua tabela.

Você precisa carregar uma cópia de seus dados na memória. Para que isso aconteça, você deve usar a medida Table.Buffer .

Se você clicar em TableBuffer, verá o que a medida faz.

Em seguida, volte ao seu conjunto de dados e clique no fx na barra de fórmulas. Você precisa adicionar o Table.Buffer na medida.

Em seguida, renomeie esta etapa para BufferedTable .

Em seguida, adicione uma coluna personalizada.

Crie a condição de filtro para os IDs

Nesta fórmula do Power Query, altere a etapa referenciada de BufferedTable para RenameColumns porque BufferedTable deve ser verificado primeiro.

Para verificar o BufferedTable, use o Table.SelectRows que retorna uma tabela que corresponde à condição selecionada.

Portanto, para cada expressão na fórmula, use Table.SelectRows e, em seguida, use BufferedTable.

Como condição de filtro, use uma função final personalizada e insira BT para Buffered Table . Verifique se a coluna ID é igual à ID do registro atual. Em seguida, acesse o registro atual usando o sublinhado como operador de acesso e faça referência à mesma coluna de ID.

Isso lhe dará uma tabela filtrada que contém apenas os IDs que correspondem ao registro atual. 

Adicione a condição de filtro para as datas

Você precisa filtrar a coluna de data porque precisa apenas das datas iguais ao registro atual.

Use estas funções para verificar a coluna de data BT:

Esta coluna precisa ser maior ou igual à data do registro atual.

Com isso, a tabela passará a conter duas linhas ao invés de três:

Verifique e classifique a coluna de data

Você precisa verificar se a classificação da coluna de data está correta usando Table.Sort . A função Table.Sort classifica uma tabela em uma ou mais colunas, dependendo dos critérios que você deseja.

A sintaxe neste exemplo é bastante complicada, então você tem que fazer um detalhamento em um assunto de mesa.

Você precisa clicar com o botão direito do mouse em uma tabela e adicioná-la como uma nova consulta.

Clicar nele fornece uma consulta personalizada que contém essa tabela.

Em seguida, clique na coluna de data e escolha Classificar em ordem crescente.

Copie toda a fórmula:

Volte para sua consulta principal e cole-a na barra de fórmulas:

Você tem que remover a referência de Table.Sort porque a tabela que precisa passar o parâmetro deve ser o resultado de Table.SelectRows.

Quando terminar, adicione as condições de classificação novamente. Depois de aplicar as alterações, sua tabela agora está classificada corretamente por data.

Use a função Table.FirstN

Para esta etapa, você só precisa de 2 registros em vez de 3.

Para corrigir isso, volte para a consulta personalizada. Clique em Manter Linhas e, em seguida, em Manter as Linhas Superiores.

Insira 2 no espaço em branco para o número de linhas.

Você notará que a função Table.FirstN aparece na barra de fórmulas. Table.FirstN retorna a(s) primeira(s) linha(s) da tabela dependendo do valor de countOrCondition .

Insira essa função, juntamente com o número de linhas necessárias na consulta principal. Inclua a tabela de tipo de função no final.

Cada objeto de tabela no conjunto agora terá 1 ou 2 linhas.

Obter a diferença de dia entre 2 valores de data

A próxima etapa é obter o número de dias entre os valores de data e extrair a data que estava no segundo registro.

Comece adicionando uma coluna personalizada. Dê-lhe o nome de Dias . Em seguida, siga esta fórmula do Power Query:

A sintaxe garante que haja 2 registros em uma tabela. Se houver apenas 1, a diferença do dia é 0 porque não há próximo registro. Se houver 2, você pode obter a diferença dos valores de data.

Em seguida, adicione o número do tipo na fórmula como esta.

Como você pode ver, você obtém uma diferença de 385 dias de 14 de setembro de 2017 a 4 de outubro de 2018.

Determinar uma etapa de peso

Em seguida, você deve calcular qual deve ser o peso para cada dia.

Você precisa adicionar uma coluna personalizada novamente, nomeá-la como WeightStep e copiar esta fórmula do Power Query:

A sintaxe verifica se a tabela tem mais de 1 linha, subtrai o peso do registro atual e divide essa diferença pelo número de dias.

Novamente, adicione o tipo de número na fórmula para que você possa ver o peso de cada dia.

Preencha o espaço

Agora que você tem o número de dias, pode criar uma lista de datas para preencher a lacuna no conjunto de dados.

Primeiro, adicione outra coluna personalizada e chame-a de DateKey . Em seguida, siga esta fórmula do Power Query:

A fórmula verifica se há uma diferença de data. Se houver, ele cria uma lista de datas entre a data inicial que é do registro atual e a expande pelo número de dias. Mas se não houver diferenças, ele retorna a data.

Este é o resultado:

Os assuntos da tabela que não tiveram diferenças tiveram sua data retornada; as tabelas que fizeram, obtiveram uma lista de datas.

Se você expandir o DateKey para novas linhas, poderá ver os registros de todos os dias.

Obtenha a projeção de peso das datas

Depois de criar uma lista de datas, você precisa obter uma projeção de peso para as datas.

Primeiro, adicione uma coluna personalizada e nomeie-a como WeightProjection . Em seguida, insira esta fórmula do Power Query:

A sintaxe obtém o valor do peso, adiciona-o ao valor DateKey, subtrai-o do valor da data e multiplica-o pelo WeightStep.

Novamente, adicione o número do tipo ao final da fórmula.

Agora você tem a projeção de peso para cada data.

Remova as colunas auxiliares: Custom, Days e WeightStep usando a fórmula abaixo e altere o formato da coluna DateKey para Date.

Conclusão

Neste tutorial, você aprendeu como preencher as lacunas em seu conjunto de dados criando valores em cada lista de datas.

Esses valores de data ajudam você a obter insights valiosos de seu modelo. Se suas tabelas carecem de informações, você pode usar esta técnica para torná-las mais abrangentes.

Pode haver dados essenciais em sua tabela que você está deixando de fora, então sempre maximize cada linha e coluna.

Tudo de bom,

melissa