Código M para Tabela de Calendário no Excel

Neste artigo vou mostrar-te como podes criar uma Tabela de calendário completa utilizando código M em Power Query. Uma tabela de calendário é fundamental em qualquer modelo de dados que pretendas usar seja em Excel ou Power Bi.

No exemplo, vou mostrar-te algumas técnicas com código M que podes usar em detrimento da interface do utilizador. Claro que utilizando os comandos da interface podes perfeitamente chegar ao mesmo resultado, no entanto com código tens vários benefícios, desde logo a performance da tua consulta, uma vez que tens muito menos passos executados. Outra vantagem de usar o código M, que vais poder ver neste artigo é que com um único passo podes gerar logo todas as colunas extra da tabela de calendário. E ainda vais poder converter o código numa função se pretenderes invocar o calendário várias vezes, por exemplo com datas diferentes!

Iniciar a consulta

Para começar vamos iniciar com uma consulta completamente em branco. Assim sendo, no Excel podes começar por iniciar o Editor do Power Query, acedendo a Dados -> Obter Dados -> Iniciar o Editor do Power Query.

No editor do Power Query iniciamos uma consulta em branco.

E de seguida acedemos ao Editor Avançado [Advanced Editor] para iniciarmos o código da Consulta.

Definir as datas de início e de fim do calendário

Vamos começar então por definir as datas de início e fim do calendário, inicialmente com valores “estáticos” e para já usando a expressão #date() que funciona de forma semelhante à função DATA no Excel. Na Data de fim acrescentamos a função Date.EndOfYear para obter o último dia do ano, da respetiva data.

O resultado como se pode ver é apenas um valor, a última data, resultante do último passo.

Criar uma Lista a partir das datas

O próximo passo é criar uma lista a partir das datas. A lista pode ser criada através da inicialização da lista com a lógica de números contínuos -> {..}. Uma vez que as datas tem de ser convertidas em números usamos a função Number.From().

O resultado é uma lista com números de serie sequenciais.

Converter a lista em tabela

Agora, vamos converter a lista numa tabela, com apenas uma coluna. Neste caso podemos usar o comando Para Tabela, mas vamos usar código, que nos permite definir uma serie de opções num único passo.

Desta forma, voltamos ao Editor Avançado…

Através do código podemos atribuir logo o nome da nova coluna “Datas” sem necessidade de adicionar este passo extra, o que aconteceria se tivéssemos a usar a interface do utilizador.

Agora o próximo passo é apenas o de alterar o tipo de dados da coluna. Aqui sim podemos usar a interface do utilizador.

No Editor Avançado, alteramos apenas o nome do passo para não conter espaços. O código até ao momento é o seguinte:

Adicionar as colunas extra da tabela

Agora este passo é importante! Aqui é que vamos ter um dos maiores benefícios do código M no cenário. Vamos adicionar uma nova coluna, mas que na verdade serão várias colunas em simultâneo. O processo pode ser feito através da interface, adicionando uma coluna de cada vez a partir da coluna de datas, e o processo é muito simples desta forma, contudo, estamos a criar vários passos e não estamos a tornar a consulta eficiente. Desta forma vamos criar a consulta com este patamar de eficiência.

Este passo pode ser feito inteiramente através do Editor Avançado, no qual vou mostrar o código final, mas aqui, vou aproveitar um pouco da interface do utilizador.

Podes acrescentar as colunas que pretenderes. No exemplo estou a utilizar funções de Data para criar cada um dos “campos” do mesmo Registo.

O código acima mostra os argumentos da função Table.AddColumn:

• Tabela -> ColunaDatas
• Nome da Coluna -> “Outras Colunas
• Expressão aplicada na coluna: Aqui, a expressão each aplica uma expressão a cada linha da tabela, que neste caso é um Registo -> []. O registo é composto por vários campos.

O resultado é apresentado na imagem a baixo.

Expandir as colunas

Agora basta expandir todas as colunas criadas. Para esta parte podemos perfeitamente usar novamente a interface do utilizador. É criado o código M por nós.

A parte do código a vermelho pode ser retirada uma vez que representa a parte onde as colunas podem ser renomeadas.

A tabela de calendário

Neste momento se visualizarmos a Consulta temos a tabela de calendário pronta. Basta apenas alterar o tipo de dados das colunas e o resultado é o esperado.

A tabela de calendário com as colunas expandidas.

Converter a consulta numa função

Este passo, é opcional! Se pretenderes colocar os anos do calendário dinâmicos e por alguma razão pretenderes reutilizar a consulta, podes sempre converter a mesma numa função.

Voltando ao editor avançado, vamos inicializar uma função.

Para criarmos a função, antes da expressão let definimos a inicialização da função.

(AnoInicio as number, AnoFim as number) =>

E aplicamos os argumentos da função que neste caso representam o Ano de Inicio e Ano de Fim do calendário no respetivo local da consulta, conforme indicado na imagem do código em baixo.

Assim que confirmamos esta alteração, a consulta transforma-se numa função, com 2 argumentos: AnoInicio e AnoFim, que são necessários para invocar a mesma.

Assim que invocada a função temos a consulta criada.

Eis o resultado da função Invocada.

E o resultado no Excel -> Menu Base -> Fechar e Carregar -> Fechar e Carregar Para…

Artigos por Categoria

• Microsoft Excel (46)
• Power Apps (16)
• Power Automate (3)
• Power BI (13)
• Power Query (14)
• Python (3)
• VBA (7)

Últimos Artigos

• Funções DAX para Análise Avançada: INDEX, OFFSET e WINDOW
• Funções REDUCE e SCAN no Excel: Entenda a Diferença
• Como Usar Parâmetros no Power BI para Relatórios Interativos
• Como usar o Copilot no Excel para criar fórmulas, resumos, gráficos e preparar impressão
• Comparação entre Claude e Copilot no Excel