Criando uma tabela calendário com linguagem M
Você já lutou para criar a tabela calendário no Power Bi?
Você não está sozinho… A boa notícia?
Você não precisa mais se preocupar! Trago uma tabela calendário em linguagem M para você, veja o conteúdo e aprenda como criar.
A linguagem M
A linguagem M é uma linguagem de programação usada principalmente no Power Query, que é uma ferramenta de transformação de dados utilizada em várias aplicações da Microsoft.
A linguagem M é projetada especificamente para manipular, transformar e combinar dados em uma etapa inicial do processo de análise de dados, antes de apresentá-los ou visualizá-los.
Deixe eu explicar com uma analogia
Imagine que você tem um monte de dados brutos, como uma pilha de papéis cheios de informações.
A linguagem M é como uma tesoura, um lápis e um borracha que você pode usar para organizar esses dados e prepará-los para análise.
Com a linguagem M, você pode:
Cortar e colar dados de diferentes fontes
Alinhar dados em diferentes formatos
Limpar dados de erros e inconsistências
Transformar dados de um formato para outro
Com os dados organizados e preparados, você pode usá-los para criar visualizações, relatórios e modelos de análise de dados.
Acessando o painel do código M
Vou aqui trazer uma passo-a-passo de como criar uma tabela calendário em Linguagem M
A tabela será criada com os nomes das colunas e valores em pt-br, modifique a gosto
Caso você já seja familiarizado, o código está no final se não…
Siga as etapas abaixo:
Na página inicial do Power bi, acesse o power query
Dentro do power query clique em “Nova Fonte” para esolher uma fonte de dados mas o que queremos é criar nossa fonte
E nas opções escolha a “Consulta Nula”
Isso fará você criar uma tabela vazia, clique na Consulta criada e selecione a opção Editor Avançado
Ao clicar em Editor avançado uma nova tela abrirá, e é aqui que vamos criar a tabela calendário
Agora vamos brincar
Vou mostrar como ficará nossa tabela no final
Incrivel né?
“… ahh… é isso…?”
…
…
tá bom , ok… seu ingrato
Como funciona a linguagem M
Antes de começar vamos entender um pouco da estrutura da linguagem M
A estrutura básica de um código M no Power Query geralmente segue uma sequência lógica de operações
No entanto, a estrutura exata pode variar dependendo da necessidade da consulta e da organização do código, como no exemplo abaixo:
let
Variavel1 = Funçao(código da função) => …,
Variavel2 = Funçao(ReferênciaVariável1,código da função) => …,
Etapa1 = …,
Etapa2 = …,in
Etapa2
Em que, a variável irá armazenar resultados ou valores que você deseja reutilizar
A função irá usar alguma lógica para trazer agum resultado
Em muitas consultas, as etapas são encadeadas sequencialmente, onde cada etapa usa o resultado da etapa anterior como uma entrada, precisando fazer uma referência antes de começar o código da função.
O código dessas funções geralmente segue sua definição e é usado para realizar transformações de dados específicas. Cada qual com suas características.
Tabela Calendário
Preparando o terreno
Agora que entendemos um pouco como funciona, vamos começar…
Primeiro vamos gerar uma função que irá retornar 3 parâmetros e essa função irá retornar uma tabela de Calendário como resultado
let fnDateTable = (DataInicial as date, DataFinal as date, MesInicioAnoFiscal as number) as table =>
Se lembre dessa tela, você a verá quando terminar o código
O código real para realizar essa operação estaria localizado após o =>.
Agora vamos criar a coluna Data mas para isso acontecer é preciso fazer uma série de transformações
Primeiro vamos usar o operador let novamente para iniciar nosso código, que vamos destrinchar o que tem na imagem a seguir
Coluna Data
let ContagemDias = Duration.Days(Duration.From(DataFinal - DataInicial)),
A Variável ContagemDias irá calcular a diferença entre as datas DataFinal e DataInicial e converte essa diferença em uma duração através da função Duration.From e em seguida tranformar essa duração em dias com a função Duration.Days.
Fonte = List.Dates(DataInicial, ContagemDias, #duration(1, 0, 0, 0)),
A Variável Fonte criará uma lista de datas a partir da data DataInicial, com um intervalo de um dia, até a data atual considerando os valores em ContagemDias.
TabelaDaLista = Table.FromList(Fonte, Splitter.SplitByNothing()),
A Variável TabelaDaLista converte a lista de datas da Variável Fonte em uma lista de listas com o código split
ex: [[“2023–07–20”], [“2023–07–21”], [“2023–07–22”]]
A partir daí a função Table.FromList transforma essa lista de listas em uma tabela com uma única coluna chamada "Column1", onde cada linha contém uma data.
TipoAlterado = Table.TransformColumnTypes(TabelaDaLista, {{"Column1", type date}}),
A Variável TipoAlterado vai alterar o tipo de dados da coluna “Column1” da tabela para type date, ou seja, garante que os valores nessa coluna sejam do tipo de data.
ColunasRenomeadas = Table.RenameColumns(TipoAlterado, {{"Column1", "Data"}}),
E por fim a variável ColunasRenomeadas através da função Table.RenameColumns irá renomear a coluna “Column1” para “Data”
Inserindo as colunas
Agora vamos inserir o restante das colunas, já que até o momento só criamos uma coluna, a de data
Já jogando o spoiler, mostrarei abaixo como ficará a estrutura
Novamente, o código está todo no final, caso não queira entender e apenas copiar e colar ou caso já saiba mexer, se for seu caso copie e cole o código e vá para a seção antes da tabela feriado, abaixo só irei explicar cada linha do código
Começando pela coluna de Ano, após a Variável vamos inserir a função Table.addColumn, referencie a etapa anterior, e use a função Date.Year para puxar o ano com base na coluna Data, inserindo o valor Int64 para tipar a coluna como número inteiro.
InserirAno = Table.AddColumn(ColunasRenomeadas, "Ano", each Date.Year([Data]), Int64.Type),
A partir daqui vamos sempre usar as função Table.AddColumn e referenciando a etapa anterior, então não ficarei me repetindo sobre isso
Para Dia e Mês o processo é bem parecido com exceção de que você vai usar as funções Date.Day e Date.Month no lugar de Date.Year
InserirDia = Table.AddColumn(InserirAno, "Dia", each Date.Day([Data]), Int64.Type), InserirMes = Table.AddColumn(InserirDia, "Mes", each Date.Month([Data]), Int64.Type),
A seguir faremos NomeMês que segue a mesma estrutura, que é usando a função Date.ToText para que possamos usar certas regras para vir um valor desejado:
“MMM” para vir o nome do mês extenso
InserirNomeMes = Table.AddColumn(InserirMes, "NomeMes", each Date.ToText([Data], "MMMM", "pt-BR"), Text.Type),
Para Criar uma coluna para Bimestre será preciso fazer uma condicional:
InserirBimestre = Table.AddColumn(InserirMesAno, "Bimestre", each if [MesDoAno] <= 2 then "1° Bim" else if [MesDoAno] <= 4 then "2° Bim" else if [MesDoAno] <= 6 then "3° Bim" else if [MesDoAno] <= 8 then "4° Bim" else if [MesDoAno] <= 10 then "5° Bim" else "6° Bim", Text.Type),
Para criar a coluna Trimestre existe uma função própria que facilita, a Date.QuarterOf Year
InserirTrimestreN = Table.AddColumn(InserirBimestre, "TrimestreN", each Date.QuarterOfYear([Data]), Int64.Type), InserirTrimestre = Table.AddColumn(InserirTrimestreN, "Trimestre", each Number.ToText([TrimestreN]) & "° Trim", Text.Type),
Para a coluna de Semestre é preciso aplicar uma condicional:
InserirSemestre = Table.AddColumn(InserirTrimestre, "Semestre", each if [TrimestreN] <= 2 then "1° Sem" else "2° Sem", Text.Type),
Agora vamos criar umas colunas especiais:a função
• Para Mês/Ano será usada Date.ToText() para converter uma data para um tipo de texto, seguido de “MMM/yyyy” que especifica que o formato do texto é mês/ano
• Os 3 seguintes seguem a mesma ideia, só mudando a referência( Bim, Trim e Sem), a função Text.From será usada para converter a coluna inicial e a segunda(Ano) em texto e entre as duas colunas terá uma concatenação “/” para separar os dois valores
InserirMesAno = Table.AddColumn(InserirSemestre, "Mês/Ano", each Date.ToText([Data], "MMM/yyyy", "pt-BR"), Text.Type), InserirBimAno = Table.AddColumn(InserirMesAno, "Bim/Ano", each Text.From([Bimestre]) & " /" & Text.From([Ano]), Text.Type), InserirTrimAno = Table.AddColumn(InserirBimAno, "Trim/Ano", each Text.From([Trimestre]) & " /" & Text.From([Ano]), Text.Type), InserirSemAno = Table.AddColumn(InserirTrimAno, "Sem/Ano", each Text.From([Semestre]) & " /" & Text.From([Ano]), Text.Type),
Para inserir DiaDaSemana temos uma função a Date.DayOf Week, contudo ela vem em número, de 0 a 6, sendo 0 = Domingo em diante até 6 = Sábado
InserirDiaDaSemana = Table.AddColumn(InserirSemAno, "DiaDaSemana", each Date.DayOfWeek([Data]), Int64.Type),
Contudo é possível criar em formato de texto também, use a função :
InserirNomeDiaDaSemana = Table.AddColumn(InserirDiaDaSemana, "NomeDiaDaSemana", each Date.ToText([Data], "dddd", "pt-BR"), Text.Type),
Em que “dddd” trará o dia da semana em formato extenso e “pt-BR” fará com que os valores venham em português
A partir dessa coluna vamos criar a coluna TipoDeDia, com a seguinte condicional:
InserirTipoDeDia = Table.AddColumn(InserirNomeDiaDaSemana, "TipoDeDia", each if [NomeDiaDaSemana] = "sábado" then "Sábado" else if [NomeDiaDaSemana] = "domingo" then "Domingo" else "Dia Útil", Text.Type),
Vamos aplicar agora nossa ultima condicional nessa tabela, para ter a coluna de Estações
InserirEstacao = Table.AddColumn(InserirTipoDeDia, "Estacao", each let DataAtual = [Data], Primavera = #date(Date.Year(DataAtual), 9, 23), Inverno = #date(Date.Year(DataAtual), 6, 21), Verão = #date(Date.Year(DataAtual), 12, 21), Outono = #date(Date.Year(DataAtual), 3, 21) in
Essa é bem chatinha, dentro da própria função vamos usar os operadores let e in para declarar algumas variáveis, uma para a Data da mesma linha da coluna e outras para declarar as estações na seguinte ordem:
O nome da estação na variável (Exemplo: Primavera)
A função #date() é usada para retornar uma data com base no ano, mês e dia
A função Date.Year() é usada para retornar o ano atual
A expressão 9, 23 é usada para definir o mês e o dia da data de início da estação
Essas fórmulas estão trazendo as estações do ano no Brasil, para outros países modifique as datas pelo amor de Deus
Após declarar cada variável para cada uma das estações, usamos o operador in para dar proseguimento na condicional:
if (DataAtual >= Primavera and DataAtual < Verão) then "Primavera" else if (DataAtual >= Outono and DataAtual < Inverno) then "Outono" else if (DataAtual >= Inverno and DataAtual < Primavera) then "Inverno" else "Verão", Text.Type),
E por fim vamos trazer mais 4 colunas de datas, para que possa ser usado em algumas situações
DiaInícioDoMes da data daquela mesma linha
DiaDoFimDoMes da data daquela mesma linha
InserirTrimestreInicio da data daquela mesma linha
InserirTrimestreFim da data daquela mesma linha
InsrirDiaInícioDoMes = Table.AddColumn(InserirEstacao, "DiaInícioDoMes", each Date.StartOfMonth([Data]), type date), InserirDiaDoFimDoMes = Table.AddColumn(InsrirDiaInícioDoMes, "DiaDoFimDoMes", each Date.EndOfMonth([Data]), Date.Type), InserirTrimestreInicio = Table.AddColumn(InserirDiaDoFimDoMes, "TrimestreInicio", each Date.StartOfQuarter([Data]), Date.Type), InserirTrimestreFim =Table.AddColumn(InserirTrimestreInicio, "TrimestreFim", each Date.EndOfQuarter([Data]), Date.Type)
Agora, pronto podemos fechar a consulta (finalmente)
in InserirTrimestreFim in fnDateTable
No fim você verá essa tela (se lembra que pedi para se lembrar dela?)
Aqui estamos criando uma tabela de datas com base na StartDate e EndDate fornecidas e no FYStartMonth(mês de início do ano fiscal) que varia de país para país, no Brasil começa em 1° de Janeiro, já nos EUA é 1° de Outubro.
Se depois de invocar a tabela queira alterar as datas, modifique na consulta da tabela invocada ou dentro do editor avançado dela
Tabela feriado
Calma, aqui é rapido, eu garanto
Primeiro, você precisa criar/carregar uma tabela de Feriados e em seguida, gerar uma nova consulta nula, use essa consulta no editor avançado e aplique um Join na sua tabela Calendário.
Os passos gerais de como fazer isso no Power Query está abaixo
Eu utilizei esse prompt para gerar os feriados nacionais, contudo talvez você precise personalizar para puxar os feriados Estaduais e Municipais
Carregar a Tabela de Feriados: No Power Query, você pode carregar a tabela de feriados de várias fontes, como um arquivo CSV, uma planilha Excel, uma tabela em um banco de dados, ou até mesmo digitando manualmente os dados.
Como podem notar copiei e colei a tabela gerada pelo GPT-chan
Nomear a Fonte de Dados: Depois de carregar e modelar a tabela de feriados, você pode nomear a fonte de dados para facilitar sua referência( Eu nomeio como feriados mas fica a gosto)
Usar a Tabela de Feriados em sua Consulta M: Agora que você tem a fonte de dados da tabela de feriados, você pode usá-la em sua consulta M.
Então você irá criar outra consulta nula e abrir o editor avançado novamente para esta consulta
Você irá agora fazer um Join da tabela de Feriados com a Tabela de Calendário
Sim, o código a seguir também está no final, abaixo do código da Calendário
Primeiro vamos precisar puxar as duas tabelas e inserir elas em uma variável
let FonteDeDadosFeriados = ..., // Sua fonte de dados da tabela de feriados TabelaDeCalendario = ..., // Sua tabela de calendário existente
Em seguida iremos realizar o Join entre as duas tabelas para criar uma nova coluna
CalendarioComFeriados = Table.NestedJoin(TabelaDeCalendario, {"Data"}, FonteDeDadosFeriados, {"Data"}, "Feriados", JoinKind.LeftOuter),
A função Nested.Join irá encontrar para cada data na tabela de calendário a correspondência na fonte de dados de feriados.
A nova coluna funcionará de forma booleana, buscando uma data similar nas duas tabelas, caso sim adicionará um valor “Feriado” caso não trará o valor “null”
Continuando, precisaremos expandir a nova coluna criada usando a função ExpandTableColumn
ExpandirFeriados = Table.ExpandTableColumn(CalendarioComFeriados, "Feriados", {"Data"}, {"DataFeriado"}),
Na próxima etapa vamos criar uma nova coluna de Tipo de dia, já que a da tabela Calendário não trazia os valores de Feriados. Para isso vamos usar a Condicional “IF”
AtualizarColunaTipoDeDia = Table.AddColumn(ExpandirFeriados, "TipoDeDiaAtualizado", each if [DataFeriado] <> null then "Feriado" else if [NomeDiaDaSemana] = "sábado" then "Sábado" else if [NomeDiaDaSemana] = "domingo" then "Domingo" else "Útil", Text.Type),
Se a nova coluna DataFeriado tiver um valor diferente de “Null” trará o Valor Feriado, se não, usará os valores da Coluna NomeDiaDaSemana da tabela Calendário para puxar os demais tipos de dias.
Por fim, irei remover colunas desnecessárias:
RemoverColunaOriginal = Table.RemoveColumns(AtualizarColunaTipoDeDia, {"TipoDeDia"}), RemoverDataFeriado = Table.RemoveColumns(RemoverColunaOriginal, {"DataFeriado"}), RemoverTrimestreN = Table.RemoveColumns(RemoverDataFeriado, {"TrimestreN"})
A antiga coluna [TipoDeDia] da tabela Calendário
E a coluna [DataFeriado]
Dessa forma, a sua nova tabela Calendário completa está pronta, então só fechar a consulta e clicar em concluído
in RemoverTrimestreN
Ps: Não esqueça de desabilitar a carga da antiga tabela Calendário e a de Feriado ao sair do Power Query e de renomear sua nova tabela
Bônus
Deixarei de presente o código dessas duas tabelas, copie e cole em um bloco de notas ou qualquer lugar para você sempre usar
let fnDateTable = (DataInicial as date, DataFinal as date, MesInicioAnoFiscal as number) as table => let ContagemDias = Duration.Days(Duration.From(DataFinal - DataInicial)), Fonte = List.Dates(DataInicial, ContagemDias, #duration(1, 0, 0, 0)), TabelaDaLista = Table.FromList(Fonte, Splitter.SplitByNothing()), TipoAlterado = Table.TransformColumnTypes(TabelaDaLista, {{"Column1", type date}}), ColunasRenomeadas = Table.RenameColumns(TipoAlterado, {{"Column1", "Data"}}), InserirAno = Table.AddColumn(ColunasRenomeadas, "Ano", each Date.Year([Data]), Int64.Type), InserirDia = Table.AddColumn(InserirAno, "Dia", each Date.Day([Data]), Int64.Type), InserirMes = Table.AddColumn(InserirDia, "Mes", each Date.Month([Data]), Int64.Type), InserirNomeMes = Table.AddColumn(InserirMes, "NomeMes", each Date.ToText([Data], "MMMM", "pt-BR"), Text.Type), InserirBimestre = Table.AddColumn(InserirNomeMes, "Bimestre", each if [Mes] <= 2 then "1° Bim" else if [Mes] <= 4 then "2° Bim" else if [Mes] <= 6 then "3° Bim" else if [Mes] <= 8 then "4° Bim" else if [Mes] <= 10 then "5° Bim" else "6° Bim", Text.Type), InserirTrimestreN = Table.AddColumn(InserirBimestre, "TrimestreN", each Date.QuarterOfYear([Data]), Int64.Type), InserirTrimestre = Table.AddColumn(InserirTrimestreN, "Trimestre", each Number.ToText([TrimestreN]) & "° Trim", Text.Type), InserirSemestre = Table.AddColumn(InserirTrimestre, "Semestre", each if [TrimestreN] <= 2 then "1° Sem" else "2° Sem", Text.Type), InserirMesAno = Table.AddColumn(InserirSemestre, "Mês/Ano", each Date.ToText([Data], "MMM/yyyy", "pt-BR"), Text.Type), InserirBimAno = Table.AddColumn(InserirMesAno, "Bim/Ano", each Text.From([Bimestre]) & " /" & Text.From([Ano]), Text.Type), InserirTrimAno = Table.AddColumn(InserirBimAno, "Trim/Ano", each Text.From([Trimestre]) & " /" & Text.From([Ano]), Text.Type), InserirSemAno = Table.AddColumn(InserirTrimAno, "Sem/Ano", each Text.From([Semestre]) & " /" & Text.From([Ano]), Text.Type), InserirDiaDaSemana = Table.AddColumn(InserirSemAno, "DiaDaSemana", each Date.DayOfWeek([Data]), Int64.Type), InserirNomeDiaDaSemana = Table.AddColumn(InserirDiaDaSemana, "NomeDiaDaSemana", each Date.ToText([Data], "dddd", "pt-BR"), Text.Type), InserirTipoDeDia = Table.AddColumn(InserirNomeDiaDaSemana, "TipoDeDia", each if [NomeDiaDaSemana] = "sábado" then "Sábado" else if [NomeDiaDaSemana] = "domingo" then "Domingo" else "Dia Útil", Text.Type), InserirEstacao = Table.AddColumn(InserirTipoDeDia, "Estacao", each let DataAtual = [Data], Primavera = #date(Date.Year(DataAtual), 9, 23), Inverno = #date(Date.Year(DataAtual), 6, 21), Verão = #date(Date.Year(DataAtual), 12, 21), Outono = #date(Date.Year(DataAtual), 3, 21) in if (DataAtual >= Primavera and DataAtual < Verão) then "Primavera" else if (DataAtual >= Outono and DataAtual < Inverno) then "Outono" else if (DataAtual >= Inverno and DataAtual < Primavera) then "Inverno" else "Verão", Text.Type), InsrirDiaInícioDoMes = Table.AddColumn(InserirEstacao, "DiaInícioDoMes", each Date.StartOfMonth([Data]), type date), InserirDiaDoFimDoMes = Table.AddColumn(InsrirDiaInícioDoMes, "DiaDoFimDoMes", each Date.EndOfMonth([Data]), Date.Type), InserirTrimestreInicio = Table.AddColumn(InserirDiaDoFimDoMes, "TrimestreInicio", each Date.StartOfQuarter([Data]), Date.Type), InserirTrimestreFim =Table.AddColumn(InserirTrimestreInicio, "TrimestreFim", each Date.EndOfQuarter([Data]), Date.Type) in InserirTrimestreFim in fnDateTablelet FonteDeDadosFeriados = Feriados, // Sua fonte de dados da tabela de feriados TabelaDeCalendario = Calendário, // Sua tabela de calendário existente CalendarioComFeriados = Table.NestedJoin(TabelaDeCalendario, {"Data"}, FonteDeDadosFeriados, {"Data"}, "Feriados", JoinKind.LeftOuter), ExpandirFeriados = Table.ExpandTableColumn(CalendarioComFeriados, "Feriados", {"Data"}, {"DataFeriado"}), AtualizarColunaTipoDeDia = Table.AddColumn(ExpandirFeriados, "TipoDeDiaAtualizado", each if [DataFeriado] <> null then "Feriado" else if [NomeDiaDaSemana] = "sábado" then "Sábado" else if [NomeDiaDaSemana] = "domingo" then "Domingo" else "Útil", Text.Type), RemoverColunaOriginal = Table.RemoveColumns(AtualizarColunaTipoDeDia, {"TipoDeDia"}), RemoverDataFeriado = Table.RemoveColumns(RemoverColunaOriginal, {"DataFeriado"}), RemoverTrimestreN = Table.RemoveColumns(RemoverDataFeriado, {"TrimestreN"}) in RemoverTrimestreN