É imprescindível que haja uma tabela calendário em seus relatórios do Power BI, contendo a dimensão tempo de forma linear e contínua, para que as funções DAX de inteligência temporal funcionem corretamente. E é muito simples cria-la através do Editor de Consultas, utilizando os recursos da Linguagem M.
Abra um novo projeto, em branco, do Power BI, e em seguida abra o Editor de Consultas:
Para isto, clique no botão “Editar Consultas”, localizado dentro do Grupo de Comandos “Dados Externos”, no menu “Página Inicial”.
Já no Editor de Consultas, clique no botão “Nova Fonte” e escolha a opção “Consulta Nula”, para abrir uma consulta em branco:
Este procedimento também pode ser realizado diretamente pela página inicial do relatório do Power BI: na página inicial, procure o Grupo de Comandos Dados Externos e clique no botão Obter Dados. No submenu, clique em Consulta Nula.
Para criarmos a tabela calendário do “zero” começaremos utilizando uma função da “Linguagem M”: List.Dates
É sempre importante lembrar que a Linguagem M é case-sensitive (difere caracteres maiúsculos e minúsculos), portanto, a função deve ser digitada exatamente da forma como está escrita, com as iniciais maiúsculas.
A consulta em branco criada aparecerá à esquerda, na seção “Consultas” do Editor de Consultas, com o nome “Consulta1”, até que você altere o mesmo.
A função deverá escrita na Barra de Fórmulas, destacada em azul na imagem anterior, e exatamente da maneira descrita anteriormente, com as inicias maiúsculas e precedida pelo sinal de igual “=”.
Uma vez corretamente digitada, você pode simplesmente apertar a tecla “Enter”, ou clicar no botão “fx” da Barra de Fórmulas, localizado à esquerda da função, para invocar a mesma.
De qualquer forma, a seguinte tela se abrirá no centro do Editor de Consultas:
Os seguintes parâmetros são exigidos:
Start: indica a data inicial da tabela calendário. O ponto de partida.
Count: indica a quantidade total de datas que a tabela calendário conterá. O seu período total de abrangência.
Step: indica a granularidade com que as datas se sucederão, se de um em um, de dois em dois dias, de três em três, ou como se desejar, dado o contexto. O ideal é 1, para que a linha do tempo fique completa e sem lacunas.
E como determinar estes parâmetros?
É lógico que há diversas formas de fazer isto, mas em princípio vamos nos ater ao meio mais convencional.
O Start, ou a data inicial da tabela, deve estar em consonância com o intervalo de tempo da ‘Tabela Fato’ do relatório, ou seja, no mínimo, deve começar na mesma data do primeiro fato registrado. Ela também pode conter datas anteriores, sem problemas. Dependerá do contexto. Para efeito de simplificação do raciocínio, sugere-se adotar o dia 1º de janeiro do mesmo ano em que esteja o primeiro registro da ‘Tabela Fato’.
O Count, que é o parâmetro que indica a abrangência da tabela calendário, é o mais complicado de se determinar: tenha sempre em mente que sua tabela fato crescerá, portanto, haverá a necessidade de se ter a linha do tempo abrangendo este período.
Supondo então que o primeiro registro da nossa ‘Tabela Fato’ seja de 2016, por exemplo, o nosso Start será 01/01/2016 e o nosso Count será o número total de dias contidos num período de 5 anos a contar desta data.
Vamos utilizar o Excel para calcular rapidamente este número de dias:
Contando 5 anos, a partir de 2016, chegamos em 2020, então, no Excel, digite na célula A1 a data de 01/01/2016 e na célula B1 a data de 31/12/2020, como mostra a próxima imagem:
Na célula C1 faça uma subtração do conteúdo da célula B1 pelo conteúdo da célula A1 e clique em “Enter”: a fórmula te retornará o número de dias que precisamos para o nosso parâmetro Count: 1826
Retornando agora à nossa função no Editor de Consultas:
Com os três parâmetros devidamente determinados na tela, basta clicar no botão “Invocar”.
A sua tela se alterará para o seguinte aspecto:
É importante observar estas alterações.
No centro, você agora estará vendo uma Lista com as datas que foram criadas. Esta lista estará denominada como “Função Invocada”. À esquerda, na seção “Consultas”, você verá que a consulta em branco, gerada incialmente, estará aparecendo como uma função, denominada “Consulta1” e precedida pelo símbolo “fx” que indica funções.
À direita, na seção “Config. Consulta”, no campo “Propriedades”, podemos renomear tanto nossa lista, quanto nossa função. Basta marcar o conteúdo da caixa “Nome” e digitar a nova informação, ou clicar com o botão direito do mouse sobre o nome na seção “Consultas” e escolher a opção “Renomear”, como mostra a próxima imagem.
Clique no cabeçalho da Lista de datas criadas e perceba que um menu contextual aparecerá na parte superior do seu Editor de Consultas, denominado “Ferramenta de Lista”:
Este menu contextual dá acesso à algumas funcionalidades adicionais, e vamos utilizar o comando “Para a Tabela”, no Grupo de Comandos “Converter”, para convertermos nossa Lista em uma Tabela e dar prosseguimento à criação da nossa tabela Calendário
Lembre-se de clicar no cabeçalho da Lista antes de clicar no comando “Para a Tabela”.
A seguinte janela se abrirá no centro da tela, e nenhuma alteração é necessária nesta etapa, bastando clicar em “Ok”:
Note que o cabeçalho da Lista se alterará:
Com este procedimento alteramos o objeto criado, passando de uma “Lista” para uma “Tabela”, com o qual podemos trabalhar de forma à atender nosso objetivo.
Clique duas vezes sobre a expressão “Column1”, para renomear o cabeçalho. Digite “Data”.
Em seguida, clique no botão “ABC123”, à esquerda do nome do cabeçalho para abrir o menu contextual mostrado na figura ao lado, e escolha o tipo “Data”, destacado em cinza e amarelo. Aqui estamos tipificando os dados como data, o que nos permitirá criar as demais colunas de nossa tabela.
É de extrema importância tipificar corretamente os dados com os quais se esteja trabalhando dentro do Editor de Consultas, pois isto permitirá a correta utilização dos mesmos posteriormente. Não seria possível, como veremos em seguida, criar outras informações temporais partindo de um dado não tipificado como “DATA” ou tipificado erroneamente como “Texto” ou outra variável.
Daqui em diante vamos inserir as demais colunas de nossa tabela. Para isto, vamos acessar o menu “Adicionar Colunas” na parte superior do Editor de Consultas, e procurar o Grupo de Comandos “Data e Hora”, localizado na extremidade direita da faixa de opções.
Nele, vamos procurar o comando “Data”, destacado em amarelo na próxima imagem:
Clique neste comando para abrir o submenu que aparece na mesma imagem.
Novamente lembrando de clicar no cabeçalho da nossa coluna “Data”, em seguida abra o conjunto “Ano” do submenu, e escolha a opção “Ano”.
Uma nova coluna aparecerá em nossa tabela, contendo apenas o ano correspondente à cada data da coluna “Data”. Ou seja, através deste comando, extraímos apenas o ano de cada data, e o inserimos numa nova coluna.
Há outras opções, mas vamos ficar apenas com esta, em princípio.
Em seguida, vamos marcar novamente a coluna “Data”, e depois vamos acessar o conjunto “Mês” do comando “Data” e escolher a opção Mês.
Com isto, uma nova coluna, com o número correspondente ao mês de cada data da coluna “Data” será inserida na tabela.
Ainda neste conjunto de opções, marque novamente a coluna “Data” e escolha a opção “Nome do Mês”.
Nossa tabela agora estará com o seguinte aspecto:
Marque novamente a coluna “Data” e volte ao comando “Data” para acessarmos o próximo conjunto de opções: DIA
Neste conjunto trabalharemos sucessivamente com as seguintes opções: Dia, Dia da Semana e Nome do Dia.
Para cada etapa será necessário retornar e marcar a coluna “Data” original, para que o comando entenda o ponto de partida, a origem da informação que está sendo inserida. Resumindo, marque a coluna “Data” e clique em “Dia”.
Marque a coluna “Data” novamente, e clique em “Dia da Semana”. Marque uma última vez a coluna “Data” e clique em “Nome do Dia”.
Ao final, nossa tabela estará com o seguinte layout:
Há diversas outras opções que podem ser inseridas através do comando “Data”, mas estas podem ser consideradas fundamentais para possibilitar a composição de uma análise de dados razoavelmente abrangente. Você poderá explorar estas outras opções posteriormente, como melhor lhe convier dentro do contexto do trabalho que esteja realizando.
Em seguida, para finalizarmos, procure no menu “Página Inicial”, o comando “Fechar e Aplicar”, destacado em amarelo na próxima imagem:
Com este comando, nós fecharemos o Editor de Consultas e retornaremos à interface principal do Power BI, onde realizaremos outros dois procedimentos simples, para completarmos nossa tabela calendário.
Por padrão, as colunas “Nome do Mês” e “Nome do Dia”, que correspondem ao mês e ao dia da semana, aparecerão listadas em ordem alfabética, uma vez que contém dados do tipo TEXTO. Mas esta ordenação não atende às demandas analíticas, e pode causar transtornos. Para resolver isto, utilizaremos o seguinte recurso:
Na seção “Campos”, localizada à direita da tela, marque o campo “Nome doMês”, que corresponde ao mês, e procure no menu “Modelagem”, na parte superior da tela, o Grupo de Comandos “Classificar” e clique no comando “Classificar por Coluna” para abrir o submenu:
Escolha a opção “Mês” que é a coluna que contém o número do mês. Desta forma os meses serão ordenados por esta sequência numérica, estabelecendo uma ordem cronológica, e não em ordem alfabética, e aparecerão corretamente ao ser utilizados em gráficos e tabelas.
Repita o mesmo procedimento para o campo “Nome do Dia” que corresponde ao dia da semana, desta vez, escolhendo no submenu a opção “Dia da Semana”, para que os dias da semana também sejam listados na ordem correta, e não em ordem alfabética.
Importante: as nomenclaturas das colunas (cabeçalhos), geradas à partir do Editor de Consultas são automáticas e oriundas da tradução direta do próprio programa. Você pode, e deve, renomeá-las para facilitar o entendimento das informações, evitando assim, erros tanto de composição das visualizações, gráficos e tabelas, quanto de interpretação das informações no processo analítico.
Como sugestão, renomeie a coluna “Mês” para “Nº Mês” e em seguida a coluna “Nome do Mês” apenas para “Mês”, pois quando estamos analisando os dados e construindo visualizações, associamos a palavra “Mês” ao nome do mês propriamente dito, e não ao seu número como estará aparecendo de fato, devido ao processo de construção da tabela e sua nomenclatura original.
Da mesma forma sugere-se fazer isto para as colunas “Dia da Semana” e “Nome do Dia”.
Como boa prática, sugere-se também ocultar da exibição do relatório, posteriormente, as colunas renomeadas que contenham os números referentes aos meses e aos dias da semana, pois na prática, elas quase nunca são usadas.
Com isto, você terá uma tabela funcional, útil e numa visualização limpa e de fácil assimilação, contribuindo para uma melhor análise dos dados em questão, e potencializando o uso das funções DAX de inteligência temporal.
Uma pergunta que fica após a finalização do processo é: terei que repetir todo este procedimento à cada vez que construir um novo projeto do Power BI?
Não.
Como dito no início, há inúmeras formas de inserir uma tabela calendário em um projeto do Power BI e aqui estamos abordando apenas uma delas.
Neste contexto, a vantagem de ter uma tabela calendário inserida diretamente dentro do modelo, é que ela se torna uma fonte de dados à menos para ser atualizada.
Uma forma interessante de não ter que repetir todo o processo à cada novo projeto, é utilizar um repositório como o OneNote para armazenar o código em “M” gerado durante o processo de construção da tabela:
Em um novo projeto, repita os primeiros passos, apenas gerando uma consulta em branco para acrescentar a função List.Dates:
Certifique-se de nomear esta função da mesma forma que está em seu código:
Em seguida abra uma nova consulta nula, na qual você poderá inserir o código armazenado no OneNote.
Clique no botão “Editor Avançado” para abrir a janela de edição do código “M”, e poder manipulá-lo:
Então selecione todo o texto que está nesta janela:
E cole por cima o seu código, previamente armazenado no OneNote:
Finalize clicando no comando “Concluído” no rodapé da janela. Após o fechamento da janela de edição do código “M”, você verá sua tabela calendário carregada no Editor de Consultas. Basta renomeá-la, e carrega-la por fim, ao modelo de dados de seu novo projeto.
