Solução de horário de trabalho entre datas do LuckyTemplates

No blog de hoje, vou demonstrar uma abordagem para o Desafio Start Stop do Access Analytic onde, usando o LuckyTemplates, o total de horas de trabalho entre datas será calculado. Você pode assistir ao vídeo completo deste tutorial na parte inferior deste blog .

A tarefa é calcular o número total de horas de um membro da equipe em cada mês e data. Apresentei datas de início e término que podem atravessar meses e levar em consideração o status lidando com possíveis erros de digitação. 

Índice

• Conjunto de dados para horas de trabalho entre datas do LuckyTemplates
• Filtrando as Linhas
• Adicionando uma coluna personalizada
• Horário de trabalho entre datas do LuckyTemplates: as variáveis
• Horário de trabalho entre datas do LuckyTemplates: projeção
• Horário de trabalho entre datas do LuckyTemplates: valores agregados
• Conclusão

Conjunto de dados para horas de trabalho entre datas do LuckyTemplates

Vamos para o editor do Power Query. 

Aqui estão nossos dados para este desafio. Temos uma tabela com colunas para Nome da equipe, Data de início, Hora de início, Data de término, Hora de término e Status.

Filtrando as Linhas

Vamos começar selecionando as linhas com base em seu status, e o editor de consultas pode nos ajudar com essa lógica. Clique na seta suspensa ao lado de Status. Selecione Filtros de texto e escolha Não começa com. 

Na janela Filter Rows que aparecerá, escreva “e”. Em seguida, clique em OK .

Nada vai acontecer ainda e tudo bem porque escrevemos um “e” minúsculo e o “Excluir” em nossos dados que começam com um “E” maiúsculo.

Para corrigir isso, usaremos o terceiro parâmetro opcional de Text.StartsWith . Na barra de fórmulas, vá até o final da função Text.StartsWith. Adicione uma vírgula e vamos inserir nosso Comparer.OrdinalIgnoreCase. Clique na marca de seleção e isso deve remover as linhas Excluir .

Claro, poderíamos ter alguém que digitou erroneamente “Excluir” sem um “E” inicial. Nesse caso, podemos criar alguma lógica adicional para acompanhar a situação. 

Podemos testar se a palavra não inclui um “X” estendendo nossa função e usando Text.Contains . Para fazer isso, adicione ou Text.Contains ([Status], “x”, Comparer.OrdinalIgnoreCase .

Observe que, em comparação com a primeira cláusula, substituímos “e” por um “x”. Continuamos ignorando o caso e colocamos um conjunto de parênteses em torno de ambas as nossas cláusulas. Clique na marca de seleção e devemos obter uma tabela sem as linhas contendo o status Excluir ou Xclude .

Adicionando uma coluna personalizada

Vamos agora para a parte principal deste desafio. Adicionaremos uma coluna personalizada usando a interface do usuário e, em seguida, alternaremos para o editor. 

Comece com o ícone da minimesa no canto superior esquerdo da mesa. Clique nele e no menu suspenso, selecione Adicionar coluna personalizada . 

Neste ponto, estamos interessados ​​apenas em trazer os dados que precisaremos mais adiante. E como trabalharemos com vários campos, vamos criar um registro usando os inicializadores de registro, representados por colchetes. 

Vamos criar variáveis ​​e atribuir as colunas disponíveis. Comece digitando SD que vamos igualar à Data de início. Podemos fazer isso clicando em Data de início na coluna à direita.

Faremos o mesmo para as variáveis ​​ST, ED e ET a serem atribuídas a Start Time, Stop Date e Stop Time , respectivamente. Em seguida, feche o registro com um colchete de fechamento. Clique em OK.

Uma coluna personalizada com os registros será adicionada à tabela. Clique no espaço em branco ao lado de Gravar e um painel de visualização será aberto. Isso trará todos os valores desta linha.

Horário de trabalho entre datas do LuckyTemplates: as variáveis

Podemos então formatar nossas variáveis ​​através da janela do Editor Avançado . É assim que a janela do Editor Avançado deve ficar. 

Então, vamos colocar nossas variáveis ​​em novas linhas separadas.

Agora, podemos expandir a lógica que precisamos. Portanto, em vez de um registro, também poderíamos usar uma expressão let aninhada aqui. Observe que qualquer um deles funcionará porque temos várias variáveis ​​ou nomes de campo com valores atribuídos a eles.

Nossa data de início está atualmente escrita como um valor de texto e precisamos transformá-la em um formato de data adequado. Para fazer isso, envolva a função Date.FromText em torno dele. 

Para a hora de início , que também é formatada como uma string, precisamos convertê-la em um número. Então, podemos dividi-lo por cem e arredondar para remover quaisquer casas decimais. 

Usando a função Number.From , divida nosso valor de Data de início por 100 . Em seguida, envolva-os na próxima função, Number.Round , e adicione , 0 no final para arredondar sem casas decimais. 

Para End Date e End Time, apenas copiaremos as mesmas funções que tínhamos em Start Date e Start Time , respectivamente, e as substituiremos pelas variáveis ​​apropriadas.

Depois que as variáveis ​​estiverem formatadas corretamente, crie outro campo em nosso registro para alguma lógica adicional. Crie uma nova linha pressionando Enter. 

Precisamos construir uma lista de datas desde a primeira data até a data final. Chamaremos essas datas de LD e usaremos a função List.Dates . 

O primeiro parâmetro desta função é a data de início que seria o nosso SD. Então, ele quer uma contagem como número ou a diferença entre a data inicial e a data final. Podemos obter isso usando a função Number.From seguida por ( ED – SD) + 1 . Observe que o “+1” não é mostrado nos próximos conjuntos de capturas de tela, mas deve haver um +1 .

O terceiro parâmetro é chamado de passo como duração e queremos um incremento de um dia. Podemos obter isso usando Duration.From (1). 

Criamos uma lista com datas desde a primeira data de início até a data de parada. O que queremos a seguir é criar uma lista de horários que acompanha isso. 

Vamos chamar isso de LT para tempos de lista. LT pode ter um período de um único dia, o que requer uma lógica ligeiramente diferente do que se abranger vários dias. Então, isso é algo para o qual temos que criar uma condição. 

Queremos que a condição diga "se nossa data de início for igual à data de término, então a hora de término menos a hora de início". Os resultados devem estar em um formato de lista, então usamos o inicializador de lista representado por { } . 

Agora, se tivermos um intervalo de datas que abrange vários dias, a primeira data será 24 menos a hora de início. Vamos adicionar isso à nossa condição como a instrução else . Novamente, formate isso como uma lista usando as chaves.

Neste ponto, não contabilizamos os outros dias inteiros, então usaremos o símbolo de e comercial (&) para anexar várias listas. Para cada dia inteiro, crie uma lista com o valor 24 representando as 24 horas que temos em um dia. Usaremos List.Repeat para isso. 

Com a função List.Repeat , crie uma lista que contenha 24 e repita-a várias vezes contando o número de dias em LD . Para conseguir isso, use List.Count (LD)   e subtraia 2 porque temos uma lista separada para nossa data de início e criaremos outra lista para a hora de término. 

Em essência, isso criará uma lista com apenas 24 horas para cada dia inteiro. 

Para o horário final, podemos anexá-lo novamente como uma lista usando e comercial. Em seguida, usando os inicializadores de lista, vamos chamar o ET. 

Até agora, temos duas grandes listas — uma lista de datas e uma lista de horários — com comprimentos iguais entre si. A partir dessas duas listas, podemos construir uma única tabela. 

Em uma nova linha, crie outra variável para a tabela que chamaremos de t e use a função Table.FromColumns . Esta função requer listas como lista e usaremos nosso LD. 

Nosso LD contém datas individuais desde a data de início até a data de parada. Podemos transformar essa lista de datas no valor da data do final do mês passando LD e chamando a função Date.EndOfMonth . Então, na próxima linha, vamos pegar nosso LT também. 

Pressione Enter para criar outra linha e definir nossa tabela como tipo table . Nossa tabela terá duas colunas, sendo a primeira coluna uma coluna de data. Então, crie uma coluna Date e chame a função Date.Type .

A segunda coluna será nossa coluna Horas e será um número inteiro. Assim, criaremos uma coluna Hrs e chamaremos Int8.Type.

Temos o colchete de fechamento para fechar nosso registro e um parêntese de fechamento para fechar nossa função Table.AddColumn . Clique em Concluído e obtemos uma lista de registros em uma coluna personalizada. 

Novamente, clique no espaço em branco ao lado de cada registro e a tabela deve abrir na parte inferior da tela. 

Observe que temos nossa Tabela t na última linha. Vamos tentar detalhar um dos registros. Clique com o botão direito no primeiro resultado e Add as New Query . 

Obteremos o mesmo resumo de antes e, se clicarmos na Tabela , devemos obter nossos valores de Data e Hrs .

Mas estamos interessados ​​principalmente na tabela de saída final. Para focar nisso, vamos remover a consulta de ajuda clicando com o botão direito do mouse em Custom e escolhendo Delete . Confirme clicando em Excluir na janela pop-up.

Depois de excluídos, podemos voltar à nossa consulta inicial e clicar novamente no espaço em branco ao lado de Registro. Ele mostrará os mesmos resultados anteriores. Mas desta vez, vamos expandir a barra de fórmulas e chamar [t] ao lado de nossos colchetes de fechamento.

Na coluna Custom, podemos ver que cada Record é alterado para Table . 

Horário de trabalho entre datas do LuckyTemplates: projeção

Na tabela que estamos examinando, há apenas duas colunas de interesse: o nome da equipe e nossa coluna personalizada. Podemos usar a projeção para reter apenas essas duas colunas desta tabela. 

Vá para a barra de fórmulas e, ao lado do parêntese de fechamento, selecione os campos que queremos manter, colocando-os entre colchetes. Em seguida, pressione a marca de seleção.

Devemos obter este resultado.

Podemos expandir a tabela aninhada clicando nos ícones de seta  ao lado de Custom . Desmarque Usar nome da coluna original como prefixo e clique em OK.

Nossa tabela deve aparecer assim.

Horário de trabalho entre datas do LuckyTemplates: valores agregados

Agora só falta agregar esses valores. Para fazer isso, selecione a coluna Staff Name e vá para Transform. 

Na janela pop-up, escolha Hrs na coluna Values ​​porque é o que queremos agregar. Em seguida, selecione Soma em Função de Valor Agregado . Em seguida, clique em OK.

Nossa saída final ficará assim. 

Criando uma tabela de datas no LuckyTemplates da maneira mais rápida possível
Comparação de tempo para tabelas de datas não padrão no LuckyTemplates
Valor de data e hora: como reduzir os segundos

Conclusão

Neste blog, você viu uma abordagem para o Desafio Start Stop do Access Analytic. Você aprendeu como calcular o número total de horas trabalhadas por cada funcionário em um mês e como lidar com possíveis erros de digitação em seus dados. 

Com essa técnica e o LuckyTemplates, as horas de trabalho entre datas podem ser facilmente calculadas em sua própria organização ou como uma prática para aprofundar seus conhecimentos e habilidades em LuckyTemplates.