Calendário Perpétuo de Feriados - POTW #12 (Solução Power Query)

Nesta postagem do blog, exploraremos minha solução para o . Para este desafio, revisitamos o desafio inaugural do problema da semana do LuckyTemplates, onde criamos um calendário de feriados perpétuos a partir de uma mistura de datas específicas e relativas. Mas desta vez, usaremos apenas o Power Query . Você pode assistir ao vídeo completo deste tutorial na parte inferior deste blog.

Se você está preso na divisão do problema em si, pode revisitar a para ver se há elementos em qualquer uma das abordagens que podem ajudá-lo com esse desafio.

Ao ler os envios, vi uma mistura de pessoas aproveitando a interface do usuário e escrevendo funções personalizadas para criar um calendário perpétuo. Tem sido divertido analisar cada entrada e, se você quiser se inspirar, encorajo-o a fazer o mesmo.

Quando eu mesmo estava trabalhando com esse cenário, acabei criando várias soluções. O que escolhi ilustra melhor como você pode não apenas dividir um problema em pedaços menores , mas também torná-los visíveis . Isso é muito útil se você encontrar um erro ou resultados inesperados.

Índice

• O ponto de partida para criar um calendário perpétuo
• Recuperando os anos da tabela de datas
• Obtendo o numeral da coluna do dia
• Invocando a função personalizada para o calendário de feriados perpétuos
• Criando uma lista de datas
• Aplicando Lógica Condicional
• Revisando o código M para o calendário de feriados perpétuos
• Conclusão

O ponto de partida para criar um calendário perpétuo

Esta mesa festiva crua é o nosso ponto de partida. Para montar uma data, precisamos de um ano, um mês e um dia para datas específicas. Só falta o ano.

Mas para datas relativas, precisamos trabalhar um pouco mais. Para começar, precisamos dividir a coluna do dia em numeral e dia da semana. Vamos revisar essas etapas.

Recuperando os anos da tabela de datas

Para tornar esta solução dinâmica, vou recuperar os anos da tabela de datas . Na barra de fórmulas, você pode ver que usei uma projeção para obter uma única tabela de coluna da minha tabela de datas. Isso, é claro, conterá duplicatas. Para removê-los, adicionei Table.Distinct .

Quando eu clicar ao lado no espaço em branco ao lado de qualquer uma dessas tabelas, você também verá uma lista com os anos distintos presentes em minha consulta de tabela de datas.

Recebemos um nome de mês , mas na verdade precisaremos de um número de mês . Agora, quando concatenamos um nome de ano a um mês, podemos usar a função Date.From para retornar o primeiro dia desse mês como um valor de data.

Ao adicionar Date.Month , ficamos apenas com o número do mês para essa data específica. Para janeiro, retorna 1. Para fevereiro, retorna 2 e assim por diante.

Obtendo o numeral da coluna do dia

Para obter o numeral da coluna do dia, decidimos não dividir a coluna e extrair o valor. Como você pode ver na barra de fórmulas, usamos Text.BeforeDelimiter e usamos esse espaço para identificar a parte direita e esquerda dessa string de texto.

Se não houver espaço, queremos retornar o que resta na coluna do dia. Portanto, para o primeiro registro, isso retorna um 1. Para o segundo registro que contém esse espaço, ele retorna a primeira parte da string de texto, a palavra Third .

Usaremos uma abordagem semelhante para obter o nome da data, mas usaremos a função List.PositionOf . Isso retorna o deslocamento baseado em zero do valor na lista passada como primeiro argumento e retorna -1 se o valor não aparecer nessa lista.

Para o primeiro registro passa o valor 1, que não está presente em nossa lista. É por isso que retorna o valor de -1. Para o segundo registro, retorna o valor Monday .

Ele passa isso para nossa função e retorna 1 porque é o segundo item da nossa lista. Em seguida, substituiremos todos os valores -1 por null .

Neste ponto, criamos todos os blocos de construção necessários para invocar a função personalizada.

Invocando a função personalizada para o calendário de feriados perpétuos

Na barra de fórmulas, podemos ver que a função recebe quatro argumentos: um ano , um mês , um numeral e um dia da semana .

Mas como ele determina esse valor de data? Usaremos o List.PositionOf novamente para obter um valor numérico para o numeral. Temos uma lista contendo primeiro, segundo, terceiro e quarto. Quando passamos o numeral para List.PositionOf , ele retorna o deslocamento baseado em zero.

Novamente, se um valor não estiver presente, ele retornará -1. Então, para o primeiro registro, estamos passando o valor 1. 1 não está presente nesta lista, por isso retorna o valor -1. Para o segundo registro, estamos passando o valor Third, que é o terceiro item da nossa lista. Isso retorna o valor de 2.

Criando uma lista de datas

Vamos agora criar uma lista de datas. Determinaremos a data de início desse mês, determinaremos o número de dias desse mês e usaremos List.Dates para criar uma lista contendo todos os dias desse mês.

Em seguida, usaremos List.Select para reter apenas as datas que estão em um dia da semana específico.

Quando clicamos ao lado de qualquer uma dessas listas, podemos ver que ela contém apenas esses quatro itens.

Aplicando Lógica Condicional

Por fim, aplicaremos a lógica condicional para construir ou recuperar uma data. Se o dia da semana for null , indicando que temos uma data específica, podemos usar o intrínseco #date para construir um valor de data.

Se o numeral for igual ao último, podemos usar a função List.Reverse para inverter a ordem na lista e extrair o primeiro valor.

Se quisermos mudar essa ordem crescente para decrescente, sempre podemos extrair a última data desse intervalo de datas. Não importa se temos quatro ou cinco elementos na lista. Ao inverter a ordem e extrair o primeiro item, sempre temos a última data.

Para todos os outros casos, usamos List.Skip .

Podemos extrair o valor com base nesta posição na lista. Para o Dia de Ação de Graças, o numeral é quarto e quarto retorna um 3. Isso ignorará os três primeiros valores da lista e sempre retornará o quarto item.

Vamos juntar tudo e ir para a consulta de solução. Para a etapa de origem, estamos chamando a tabela de datas de feriados RAW. Em seguida, adicionaremos uma coluna que retorna uma lista com datas.

Revisando o código M para o calendário de feriados perpétuos

Vamos verificar o código M da coluna. Para cada linha da tabela, aninhamos uma tabela contendo os anos distintos da tabela de datas. Em seguida, transformamos os valores nessa coluna de ano chamando a função personalizada.

Obviamente, para poder invocar a função personalizada, primeiro precisamos fazer algumas configurações. Temos que obter o número do mês , o valor numérico e, claro, o dia da semana . A partir dessa tabela aninhada, mantivemos a lista com as datas.

Quando clicamos ao lado no espaço em branco, podemos ver uma prévia dessa lista abaixo.

A próxima etapa é expandir essa lista e adicionar o CelebratedOnDate .

Esta é a lógica que usamos neste cálculo: se a data for um sábado, subtrairemos um dia para cairmos em uma sexta-feira. Se for domingo, adicionaremos um dia para pousar na segunda-feira. Se não for sábado ou domingo, queremos um nulo.

Por fim, o último passo é alterar os tipos.

Conclusão

Apenas um lembrete rápido de que é uma série contínua que acontece toda 1ª e 3ª quarta-feira no fórum LuckyTemplates. O problema publicado na 1ª semana requer uma solução baseada em DAX, enquanto o problema da 3ª semana requer uma solução baseada em Power Query.

Se você gostou do conteúdo abordado neste POTW, assine o canal de TV LuckyTemplates para obter mais informações. Temos uma grande quantidade de conteúdo sendo publicado o tempo todo, meu e de vários criadores de conteúdo, todos dedicados a melhorar a maneira como você usa o LuckyTemplates e a Power Platform.

melissa