Power Query M: padrão 0-1 alternado na tabela de datas

Este tutorial se concentra no uso da função number.mod do Power Query M para criar um padrão 0-1 alternado na tabela Date. A solução que vou mostrar veio de um dos usuários avançados da comunidade, Rajesh, da LuckyTemplates. Você pode assistir ao vídeo completo deste tutorial na parte inferior deste blog.

O objetivo é gerar uma sequência de quatro zeros alternados por quatro uns e repeti-la ao longo do dia.

Rajesh foi certeiro em sua análise e detalhamento do problema usando a função Power Query M para resolvê-lo. Dito isso, vamos para o Power Query.

Índice

• A função Power Query M Number.Mod
• Criando uma coluna personalizada para o padrão M do Power Query
• Criando uma construção IF-THEN-ELSE
• Conclusão

A função Power Query M Number.Mod

O Power Query M, especificamente a função number.mod, será o núcleo da nossa solução. O que isso faz?

Você pode verificar a documentação interna de qualquer função M inserindo seu nome sem parênteses, conforme mostrado aqui na barra de fórmulas. Esta função retorna o resto de uma divisão inteira. Leva até três parâmetros, os dois primeiros, um número, e o divisor, que também é um número.

Agora vamos começar. Selecionarei minha consulta de amostra que contém um intervalo de datas que preparei.

Em muitos casos, number.mod é usado em combinação com uma coluna de índice que é então usada como seu primeiro parâmetro. Mas não vou fazer isso porque, se você pensar bem, o valor da data é um número inteiro. Portanto, criar um índice que compartilhe a mesma granularidade da coluna Data, onde cada linha tem um valor único, é algo que podemos evitar.

Tudo o que precisamos fazer para imitar um índice é subtrair a primeira data em nossa tabela Date de cada data na coluna Date. Para fazer isso, podemos armazenar o valor da data de início em uma variável para que possamos usá-la repetidamente. Acho que o Editor Avançado é o melhor lugar para fazer isso.

Então, abrirei a janela do Editor Avançado e criarei uma nova variável. Vamos chamá-lo de StartDate . Eu codifiquei minha primeira data aqui, então vou apenas copiá-la e colá-la. Não se esqueça da vírgula no final e agora podemos substituir essa primeira data por nossa variável de data de início.

Como você pode ver, nada mudou.

Criando uma coluna personalizada para o padrão M do Power Query

Por meio dessa minitabela, vamos adicionar uma coluna personalizada.

Vamos chamar essa coluna de Pattern e inserir a função M do Power Query, number.mod . Você vê os dois parâmetros aqui, um número como um número anulável e um divisor como um número anulável. Portanto, requer dois valores de tipo numérico como parâmetros.

Agora não temos números, certo? Temos datas e não há conversão automática de tipo nelas. Portanto, precisamos obter o número do nosso valor de data. Então, digitamos aqui, number.from , nossa coluna Date e subtraímos nossa variável StartDate.

Agora, digamos que a primeira data em nossa tabela Date retorne um, então nossa StartDate também retornará um. E assim, um menos um é igual a zero. Quero que nossa numeração de índice comece com um em vez de zero. Então teremos que somar um de volta mais um. Agora um menos um é igual a zero mais um retorna um para o primeiro registro.

Para o segundo registro, obtemos um valor dois (2) para a data na coluna Date, menos um (1) de nosso valor StartDate que é igual a um, mais o que estamos adicionando de volta retorna um dois e assim por diante . Nosso padrão de repetição foi baseado em uma série de quatro valores alternados. Então, como divisor, vou passar o valor quatro (4).

Vamos ver o que isso faz. Na linha quatro, obtemos zero, assim como na linha oito. Portanto, cada quarta ocorrência retorna um valor zero para esse registro.

Criando uma construção IF-THEN-ELSE

Vamos voltar à caixa de diálogo do cliente e estender a lógica que escrevemos até agora. Com exceção do divisor, precisamos repetir a mesma lógica para poder identificar uma oitava ocorrência.

No entanto, nessa oitava linha, queremos um valor diferente. Portanto, para fazer uma distinção entre uma quarta e uma oitava ocorrência, podemos usar uma construção IF-THEN-ELSE. Dessa forma, podemos retornar um valor para o primeiro teste lógico que retorne um verdadeiro.

Portanto , se , e eu colar de volta nosso código aqui (exceto para o divisor), altere esse divisor para um oito para que a oitava linha retorne zero. Portanto, se for igual a zero, isso significa que falhará em seu caminho para verdadeiro ou falso. Então queremos um (1). Caso contrário , se nossa lógica aqui identificar que uma quarta linha é igual a zero, queremos zero (0). Caso contrário, não queremos obter um valor de volta. Então, vamos adicionar um null .

Agora, vamos verificar nossa lógica novamente. Assim, em cada quarta ocorrência, obtemos esse zero. E então, no oitavo, conseguimos um. Em seguida, obtemos zero e um, e assim por diante.

Então isso parece bom. Tudo o que temos a fazer agora é preencher esses valores.

Portanto, dentro da barra de fórmulas, adicionaremos um Table.FillUp no início e alguns parênteses de abertura. No final do nosso código, adicionamos o tipo number – também precisamos adicionar uma coluna como uma lista. Então, vamos vírgula , então usamos os colchetes para o inicializador de lista e como um valor de texto passamos em nosso nome de coluna, Pattern . Não se esqueça dos parênteses de fechamento e pressione OK.

Guia para iniciantes do código M no LuckyTemplates
Função M para tabela de datas – como adicionar um parâmetro no Editor de consultas
Classificando colunas da tabela de datas no LuckyTemplates

Conclusão

Este é um cenário único e, honestamente, não consigo pensar em um aplicativo para esse padrão específico, mas espero que você possa ver o potencial de criar sequências usando a função M do Power Query, number.mod.

Para mais detalhes, assista ao vídeo completo abaixo para este tutorial.

Tudo de bom!

melissa