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ê.
