Tabela dCalendário no PowerQuery Excel

Neste artigo veremos o passo-a-passo para criar uma tabela dimensão calendário dcalendario no PowerQuery no Excel

Power Query é uma ferramenta de limpeza, transformação e carregamento de dados (ETL) incluída no Microsoft Excel e no Power BI Desktop. Ele permite que os usuários conectem-se a várias fontes de dados, como planilhas, bancos de dados e arquivos de texto, e realizem operações de limpeza, transformação e agregação de dados para preparar esses dados para análise.

Uma das funcionalidades do Power Query é a capacidade de criar tabelas de calendário. Isso permite que os usuários geram uma tabela com todas as datas de um determinado período, como um ano ou mês, e adicionem colunas adicionais, como dia da semana e feriados, para usar como uma dimensão em seus relatórios. Isso é útil para análises de dados que requerem uma visão temporal.

Tabela Calendário?

Ao analisar dados na grande maioria das vezes teremos uma coluna de data, deste modo a tabela Calendário é de extrema importância em um contexto de BI, pois, através dela podemos utilizar expressões DAX próprias para análises temporais, além de, criarmos filtros personalizados por períodos, como:

• Semestres
• Trimestres
• Anos
• Meses e Anos
• Meses
• Dias da Semana.

Logo abaixo você terá o código completo, basta copiar e colar no editor avançado do Power Query, disponível tanto no Excel como no Power BI.

Porém, copiar o código, colar e alterar apenas no local indicado, não fará com que você aprenda e entenda o que está sendo feito. Logo, observe as etapas, veja o que cada etapa está criando, entenda este fluxo de acontecimentos para que, entendendo os conceitos, possa moldar esta importante dimensão de acordo com as suas necessidades.

Atente-se aos campos que você deve alterar (destacados em azul).

Os campos escritos em verde, são comentários explicando o que será realizado na etapa logo abaixo, então:

• Leia os comentários
• Entenda o que está sendo feito
• Explore as funções da Linguagem M, analisando o script abaixo.

Fique claro que esta não é a única forma de se criar a tabela dCalendário, para cada regra de negócio deve-se ajustar o modelo.

Vamos ao que interessa dCalendario no PowerQuery:

let
 
//Para iniciar a tabela Calendário, vamos obter algumas informações, como Data mínima e máxima, duração entre estas datas (nada mais é que a diferença entre Data Máxima e Mínima)
  //Criaremos nossas variáveis
   //Devemos descobrir a Data Mínima, para isto utilizaremos a função List.Min
    //Informamos na List.Min a tabela e Coluna de Datas 
DataMinima = List.Min(Tabela[ColData]),
 
//Com a DataMínima, iremos extrair o Ano desta data
AnoMinimo = Date.Year (DataMinima),
 
//Agora, iremos descobrir qual é a data máxima (caso tenha uma tabela que terá sempre a maior data utilize ela)
  //caso contrário, utilize as mesmas tabelas utilizadas para buscar a DataMinima
DataMaxima = List.Max(Tabela[ColData]),
    
//Com a DataMaxima, iremos extrair apenas o Ano desta data
AnoMaximo = Date.Year(DataMaxima),
 
//Agora devemos descobrir a duração de dias entre DataMaxima e DataMínima
   //Utilizaremos a função Duration.Days: ela pede DataMáxima, DataMínima
      //devemos utilizar a expressão #date(ano,mes,dia)
         //note que para data máxima, informaremos a variável AnoMaximo, seguida o nº 12 e nº 31 (12 referente a dezembro e 31 ao último dia do mês de dezembro)
           //note que para data mínima, informaremos a variável AnoMinimo, seguida o nº 1 e nº 1 (1 referente a janeiro e 1 ao primeiro dia do mês de janeiro)
            //por fim adicionaremos 1 dia, para realizar a correção da subtração e retornar o intervalo correto
 Duracao= Duration.Days(#date(AnoMaximo,12,31) - #date(AnoMinimo,1,1))+1,
 
//Criar a Tabela Calendario, para isto inserimos a função List.Dates, que nos pede: Data Inicial; Qtd de dias a partir da data inicial; qual o intervalo entre cada data (INFORMAMOS VIA EXPRESSÃO #duration(dias,horas,minutos,segundos).
Fonte = List.Dates(#date(AnoMinimo, 1, 1), Duracao, #duration(1, 0, 0, 0)),
 
//Após criar a Calendario, devemos converter para Tabela
ConverterParaTabela = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 
//Iremos agora tipar a Column1 como DATA    
 TransformarEmData = Table.TransformColumnTypes(ConverterParaTabela,{{"Column1", type date}}),
 
//Agora iremos renomear nossa Column1, para Data
NomeAlterado = Table.RenameColumns(TransformarEmData,{{"Column1", "Data"}}),
 
//Com a coluna Data selecionada, vá em Adicionar Coluna -> Data -> Ano
ColAno = Table.AddColumn(NomeAlterado, "Ano", each Date.Year([Data]), Int64.Type),
    
//Com a coluna Data selecionada, vá em Adicionar Coluna -> Mês -> Mês
ColMes = Table.AddColumn(ColAno, "Mês", each Date.Month([Data]), Int64.Type),
    
//Com a coluna Data selecionada, vá em Adicionar Coluna -> Data -> Nome do Mês
NomeMes = Table.AddColumn(ColMes, "Nome do Mês", each Date.MonthName([Data]), type text),
 
//Com a coluna NomeMes selecionada, vá em Transformar -> Formato -> Colocar Cada Palavra em Maiúscula
NomeMesMaiusculo = Table.TransformColumns(NomeMes,{{"Nome do Mês", Text.Proper, type text}}),
    
//Iremos abreviar o Nome do Mês para 3 caracteres, selecione a coluna Nome do Mês -> Transformar -> Extrair -> Primeiros Caracteres -> 3
MesAbrev = Table.TransformColumns(NomeMesMaiusculo, {{"Nome do Mês", each Text.Start(_, 3), type text}}),
 
//Com a coluna Data selecionada, vá em Adicionar Coluna -> Data -> Dia
ColDia = Table.AddColumn(MesAbrev, "Dia", each Date.Day([Data]), Int64.Type),
 
//Iremos adiconar uma coluna condicional, vá em Adicionar Coluna -> Coluna Personalizada:
  //nas clausulas faça os testes lógicos:
    //Mês <=6 Sem1
      //caso contrário Sem2
ColSemestre = Table.AddColumn(ColDia, "Semestre", each if [Mês] <= 6 then "Sem1" else "Sem2"),
 
//Iremos adicionar uma coluna para trimestre, vá em Adicionar Coluna -> Coluna Personalizada: 
  //nas clausulas faça os testes lógicos: 
    //mês <= 3 Tri1
      //mês <= 6 Tri2
       //mês <=9 Tri3 caso contrário Tri4
ColTrimestre = Table.AddColumn(ColSemestre, "Trimestre", each if [Mês] <= 3 then "Tri1" else if [Mês] <= 6 then "Tri2" else if [Mês] <= 9 then "Tri3" else "Tri4"),
    
//Iremos adicionar a coluna MesAno com uma coluna condicional
  //Selecione as colunas Mês e Ano (nesta ordem), vá em Adicionar Coluna -> Coluna de exemplo
    //Na coluna que irá surgir indique o formato que deseja criar Ex: Jan-2018
      //logo surgirá para demais linhas os exemplos preenchidos, confira, estando tudo certo clique em OK
ColMesAno = Table.AddColumn(ColTrimestre, "Mês-Ano", each Text.Combine({[Nome do Mês], "-", Text.From([Ano], "pt-BR")}), type text),
    
//Iremos adicionar uma coluna para ordenar a coluna MesAno criada na etapa anterior
  //Vá em Adicionar Coluna -> Coluna Personalizada. Nomeie como IDMesAno ou MesAnoClassificacao
    //Insira o seguinte cálculo = Ano * 100 + Mês
ColMesAnoClassificacao = Table.AddColumn(ColMesAno, "MesAnoClassificacao", each [Ano]*100+[Mês]),
    #"Tipo Alterado" = Table.TransformColumnTypes(ColMesAnoClassificacao,{{"Semestre", type text}, {"Trimestre", type text}, {"MesAnoClassificacao", Int64.Type}})
 
in
    #"Tipo Alterado"

Espero ter contribuído com seu aprendizado, agora é com você.