Tabela Calendário no Power BI.
Vamos falar mais uma vez sobre como fazer uma tabela d_calendário no Power BI? E como criar no Power Query.
Dessa vez, passarei o passo a passo com origem na tabela Fato!
Na publicação anterior a criação da d_calendário não está vinculada na tabela Fato, é algo manual e sempre que você precisar trabalhar em outro projeto terá que adaptar as datas iniciais e finais.
Neste passo-a-passo você conseguirá adaptar aos projetos sem se preocupar tanto com as datas iniciais e finais da Fato.
Bora lá fazer essa belezura.
Não me preocupei em colocar os trimestres, semestres e outros detalhes que dependendo do projeto podem precisar.
Vamos pegar a data inicial:
DataMin = List.Min(Fato[data])
Na sequência selecionamos a data final, lembrando que será atualizado automaticamente:
DataMax = List.Max(Fato[data])
E agora com as duas datas, conseguimos calcular a quantidade de dias no período:
QtdeDias = Duration.Days(DataMax - DataMin) + 1
É necessário colocar o ” + 1 ” para que considere todos os dias do período, caso esqueça, o último dia (mais atual) ficará de fora, faça o teste para ver… rsrsrs
No início sofria com isso, o “(Em branco)” sempre aparecia nos filtros do calendário.
Antes de fazer a lista com as datas eu “guardo” o ano da data inicial. Faço isso porque, depois de quebrar muito a cabeça, consegui encontrar uma maneira de deixar a criação do índice da coluna “mês/ano” automática. O mestre Leonardo Karpinski ensina em um vídeo como fazer isso, eu apenas deixei de uma forma que não precisa digitar o ano inicial:
AnoMin = Date.Year(DataMin)
Você entenderá mais pra frente o por quê desse “AnoMin”.
Já com todos os valores necessários para criar a lista de datas, bora começar a ver a d_calendario:
ListaDatas = List.Dates(DataMin, QtdeDias, #duration(1,0,0,0))
Lembrando que estou trabalhando com DIAS, caso precise trabalhar com horas, minutos e segundos você precisará utilizar o seguinte:
ListaDatas2 = List.DateTimes(DataMin, QtdeDias, #duration(0,0,1,0))
Preste atenção na função #duration, ela considera dias, horas, minutos e segundos, nessa sequência.
No ListaDatas faço a divisão entre dias, duração de 1 dia, já no ListaDatas2 a duração está por minutos, assim terá uma precisão maior. Dependendo do projeto você pode precisar desse ou até com divisão de segundos.
Depois de criar a lista é preciso transformar ela em tabela:
Tabela = Table.FromList(ListaDatas, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
Após ter a tabela, você fica à vontade de trabalhar conforme está acostumado, aqui passarei alguns passos: (transformar a coluna em data, adicionar coluna ano e coluna mês)
Tipo_Data = Table.TransformColumnTypes(Tabela,{{"Column1", type date}})
#"Colunas Renomeadas" = Table.RenameColumns(Tipo_Data,{{"Column1", "Data"}})
Ano = Table.AddColumn(#"Colunas Renomeadas", "ano", each Date.Year([Data]), Int64.Type)
Mes = Table.AddColumn(Ano, "mes", each Date.Month([Data]), Int64.Type)
Na sequência crio a coluna com a descrição do mês:
Descricao_Mes = Table.AddColumn(Mes, "nome_mes", each Text.Proper(Text.Start(Date.MonthName([Data]),3)), type text)
Eu gosto de trabalhar com o nome do mês abreviado e com a primeira letra maiúscula, para isso resumo alguns passos em uma linha.
Detalhando o código:
- “Text.Proper” coloca a primeira letra em maiúsculo;
- “Text.Start” seleciona a quantidade de caracteres que desejo, nesse caso são 3; e por fim
- “Date.MonthName” pega o nome do mês da coluna Data.
São três etapas do Power Query resumidas em uma.
Com a descrição do mês disponível, crio a coluna “mês/ano”, só que aqui tem uma pegadinha, porque o mês é TEXTO e ano é NÚMERO, para isso utilizo o “Text.From” e informo que essa coluna é tipo TEXTO, conforme código abaixo:
Descricao_Mes_Ano = Table.AddColumn(Descricao_Mes, "mes_ano", each [nome_mes]&"/"&Text.From([ano]))
Tipo_Mes_Ano = Table.TransformColumnTypes(Descricao_Mes_Ano,{{"mes_ano", type text}})
Aí surge um problema, como classificar a coluna “mês/ano” já que é texto?
Com a ajuda o Karpinski que resolvi meu problema, só que mesmo assim dependia de um insert manual nessa etapa do ano inicial, foi por causa dessa dependência que busquei alternativas e cheguei nesse código para criar o índice e já aproveito para classificar como número inteiro:
Indice_Mes_Ano = Table.AddColumn(Tipo_Mes_Ano, "indice_mes_ano", each ([ano]-AnoMin)*12+[mes])
Tipo_Indice_Mes_Ano = Table.TransformColumnTypes(Indice_Mes_Ano,{{"indice_mes_ano", Int64.Type}})
Lembra do “AnoMin”, apareceu aqui, com ele o código fica mais limpo e o que precisa ser mudado são somente as duas primeiras linhas onde as referências são as datas da tabela Fato.
Para finalizar, segue código completo:
let
DataMin = List.Min(Fato[data]),
DataMax = List.Max(Fato[data]),
QtdeDias = Duration.Days(DataMax - DataMin) +1,
AnoMin = Date.Year(DataMin),
ListaDatas = List.Dates(DataMin, QtdeDias, #duration(1,0,0,0)),
Tabela = Table.FromList(ListaDatas, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Tipo_Data = Table.TransformColumnTypes(Tabela,{{"Column1", type date}}),
#"Colunas Renomeadas" = Table.RenameColumns(Tipo_Data,{{"Column1", "Data"}}),
Ano = Table.AddColumn(#"Colunas Renomeadas", "ano", each Date.Year([Data]), Int64.Type),
Mes = Table.AddColumn(Ano, "mes", each Date.Month([Data]), Int64.Type),
Descricao_Mes = Table.AddColumn(Mes, "nome_mes", each Text.Proper(Text.Start(Date.MonthName([Data]),3)), type text),
Descricao_Mes_Ano = Table.AddColumn(Descricao_Mes, "mes_ano", each [nome_mes]&"/"&Text.From([ano])),
Tipo_Mes_Ano = Table.TransformColumnTypes(Descricao_Mes_Ano,{{"mes_ano", type text}}),
Indice_Mes_Ano = Table.AddColumn(Tipo_Mes_Ano, "indice_mes_ano", each ([ano]-AnoMin)*12+[mes]),
Tipo_Indice_Mes_Ano = Table.TransformColumnTypes(Indice_Mes_Ano,{{"indice_mes_ano", Int64.Type}})
in
Tipo_Indice_Mes_Ano