Olá, pessoal!

Tudo bem com vocês?

Hoje, vou mostrar como criar uma tabela calendário utilizando a linguagem M.

Eu expliquei em um post anterior o porquê da necessidade de uma tabela de tempo no seu modelo. Se você ainda não leu, sugiro a leitura antes de prosseguir este post. 🙂

Power BI – Calendar X CalendarAuto

Recentemente participei de uma live mencionando as boas práticas de  desenvolvimento no Power BI, sendo a criação da tabela calendário um ótimo ponto de partida.

Se você não viu, vai ver! Rs

Só clicar aqui –> Live Performance e Produtividade no Power BI

Pensando nisso, resolvi criar a minha própria versão, com comentários, sequência e os códigos propícios, quando existentes. Não precisamos ficar bolando mil e uma formas de criar uma máscara para um campo se já existe um função que faça isso. Claro, fica mais fácil se sabemos da existência da função. Rs.

Eu, por exemplo, detesto número sem o zero na frente, como por exemplo dia 1 e dia 10. Como o 10 tem duas casas, eu prefiro colocar 01, assim os dois ficam alinhados. Mas, isso é gosto pessoal. Até um tempo atrás eu usava IF para tratar isso. Após descobrir a existência da função Text.PadStart, nem preciso dizer que na hora fui ajustar o código. O quanto ele melhorou com essa simples mudança!

Enfim, sem mais delongas, vamos ao passo-a-passo.

Clique em Editar Consulta (Edit Queries);

Com o botão direito do mouse, na área de Consultas (Queries), Clique em Nova Consulta (New Query) e, em seguida, Consulta em Branco (Blank Query);

Clique em Editor Avançado (Advanced Editor);

Cole o código abaixo.

let
// Criando Range de Data Entre 01/01/2016 e Hoje
Fonte = #date(2016, 1, 1),
ListaDatas = List.Dates(Fonte,Number.From(DateTime.LocalNow())-Number.From(Fonte),#duration(1,0,0,0)),
#”ConverteTabela”= Table.FromList(ListaDatas, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

// Renomeando e formatando a coluna para Data
#”Renomeia Coluna para Data” = Table.RenameColumns(ConverteTabela,{{“Column1”, “Data”}}),
#”Formata Coluna como Data” = Table.TransformColumnTypes(#”Renomeia Coluna para Data”,{{“Data”, type date}}),

// Dia
DiaNum = Table.AddColumn(#”Formata Coluna como Data”, “DiaNum”, each Date.Day([Data]), Int64.Type),
DiaComZero = Table.AddColumn(DiaNum, “DiaComZero”, each Text.PadStart(Text.From(Date.Day([Data])),2,”0″), type text),
DiaNome = Table.AddColumn(DiaComZero, “DiaNome”, each “Dia ” & [DiaComZero] , type text),
DiaAno = Table.AddColumn(DiaNome, “DiaAno”, each Date.DayOfYear([Data]), type text),

// Mês
MesNum = Table.AddColumn(DiaAno, “MesNum”, each Date.Month([Data]), Int64.Type),
MesComZero = Table.AddColumn(MesNum, “MesComZero”, each Text.PadStart(Text.From(Date.Month([Data])),2,”0″), type text),
MesNome = Table.AddColumn(MesComZero, “MesNome”, each Date.MonthName([Data]), type text),
MesNomeAbrev = Table.AddColumn(MesNome, “MesNomeAbrev”, each Date.ToText([Data],”MMM”), type text),
MesNomeAbrevAno = Table.AddColumn(MesNomeAbrev, “MesNomeAbrevAno”, each Date.ToText([Data],”MMM-yyyy”), type text),
MesExtenso = Table.AddColumn(MesNomeAbrevAno, “Mes Extenso”, each “Mês ” & [MesComZero]),

// Ano
Ano = Table.AddColumn(MesExtenso, “Ano”, each Date.Year([Data]), type text),
#”Ano/Mes” = Table.AddColumn(Ano, “Ano/Mes”, each Date.ToText([Data],”yyyy/MM”), type text),

// Semana
DiaSemana = Table.AddColumn(#”Ano/Mes”, “DiaSemana”, each Date.DayOfWeek([Data]), type text),
DiaSemanaAbrev = Table.AddColumn(DiaSemana, “DiaSemanaAbrev”, each Date.ToText([Data],”ddd”), type text),
DiaSemanaNome = Table.AddColumn(DiaSemanaAbrev, “DiaSemanaNome”, each Date.ToText([Data],”dddd”), type text),
SemanaAno = Table.AddColumn(DiaSemanaNome, “SemanaAno”, each Text.PadStart(Text.From(Date.WeekOfYear([Data])),2,”0″), type text),
#”Semana, Ano” = Table.AddColumn(SemanaAno, “Semana, Ano”, each “Semana ” & [SemanaAno] & “, ” & Text.From([Ano]), type text),
SemanaExtenso = Table.AddColumn(#”Semana, Ano”, “SemanaExtenso”, each “Semana ” & [SemanaAno], type text),
SemanaMes = Table.AddColumn(SemanaExtenso, “SemanaMes”, each Date.WeekOfMonth([Data]), type text),

// Bimestre
Bimestre = Table.AddColumn(SemanaMes, “Bimestre”,
each if [MesNum] <= 2 then 1
else if [MesNum] <= 4 then 2
else if [MesNum] <= 6 then 3
else if [MesNum] <= 8 then 4
else if [MesNum] <= 10 then 5
else 6 , type text),

// Semestre
Semestre = Table.AddColumn(Bimestre, “Semestre”,
each if [MesNum] <= 6 then 1
else 2, type text),

// Trimestre
TrimestreNum = Table.AddColumn(Semestre, “TrimestreNum”, each Date.QuarterOfYear([Data]), type text),
TrimestreNom = Table.AddColumn(TrimestreNum, “TrimestreNom”, each Number.ToText([TrimestreNum]) & “º” & ” – Trimestre”, type text),

// Data Inicio e Fim mês
InicioMes = Table.AddColumn(TrimestreNom, “InicioMes”, each Date.StartOfMonth([Data]), type text),
FimMes = Table.AddColumn(InicioMes, “FimMes”, each Date.EndOfMonth([Data]), type text),

//Calendário Ano
#”Calendario Ano” = Table.AddColumn(FimMes, “Calendario Ano”, each “Calendário ” & Text.From([Ano]), type text),

//Flags
FlagFinalSemana = Table.AddColumn(#”Calendario Ano”, “FinalSemana”, each if([DiaSemana] = 0 or [DiaSemana] = 6) then “Sim” else “Não”, type text),
FlagHoje = Table.AddColumn(FlagFinalSemana, “FlagHoje”, each Date.IsInCurrentDay([Data]), type logical),
FlagMesAtual = Table.AddColumn(FlagHoje, “FlagMesAtual”, each Date.IsInCurrentMonth([Data]), type logical),
FlagTrimestreAtual = Table.AddColumn(FlagMesAtual, “FlagTrimestreAtual”, each Date.IsInCurrentQuarter([Data]), type logical),
FlagSemanaAtual = Table.AddColumn(FlagTrimestreAtual, “FlagSemanaAtual”, each Date.IsInCurrentWeek([Data]), type logical),
FlagAnoAtual = Table.AddColumn(FlagSemanaAtual, “FlagAnoAtual”, each Date.IsInCurrentYear([Data]), type logical),
FlagYTD = Table.AddColumn(FlagAnoAtual, “YTD”, each if [FlagAnoAtual] = true and [Data] <= Date.From(DateTime.LocalNow()) then true else false, type logical)
in
FlagYTD

Basicamente, a tabela foi organizada por períodos, facilitando a compreensão e uso da mesma.

Se pssuir alguma dúvida sobre o código, não deixe de perguntar! A sua dúvida pode ser a solução de outra pessoa!

Att.
Meirieli Ribeiro

 

Power BI – Criando uma tabela calendário em M
Classificado como:        

14 ideias sobre “Power BI – Criando uma tabela calendário em M

    • 15 de abril de 2019 em 11:35 PM
      Permalink

      Deu algum erro?

      Se sim, pode postar aqui?

      Resposta
  • 22 de março de 2019 em 8:07 PM
    Permalink

    Sensacional, consegui e deu certo, parabéns, uma duvida a ultima data vai ser sempre = hoje

    Resposta
    • 15 de abril de 2019 em 11:36 PM
      Permalink

      Não. Vc pode trocar ou criar alguma condição para ir até a data que vc precisa. 🙂

      Resposta
  • 22 de março de 2019 em 8:06 PM
    Permalink

    Sensacional, consegui e deu certo, parabéns, uma duvida a ultima data vai ser sempre = hoje?

    Resposta
    • 15 de abril de 2019 em 11:36 PM
      Permalink

      Não. Vc pode trocar ou criar alguma condição para ir até a data que vc precisa. 🙂

      Resposta
  • 2 de fevereiro de 2019 em 1:14 PM
    Permalink

    Quando a consulta é convertida para tabela, os valores das datas são fixados, ou seja, a tabela possui as datas do período inicial que foi fixado até a data atual (DateTime.LocalNow()).
    Qual o procedimento que deve ser adotado para que seja adicionado, sempre, os dias posteriores a data da conversão da consulta em tabela? Isto é possível ou a tabela é estática?

    Resposta
    • 13 de fevereiro de 2019 em 8:09 PM
      Permalink

      João,

      O valor utilizado foi apenas para o demo da criação da tabela.
      Pode-se criar uma fórmula para pegar uma data inicial dinâmica. 🙂

      Resposta
  • 5 de novembro de 2018 em 5:39 PM
    Permalink

    Erro:
    “Expression.SyntaxError: Esperava-se o token Identifier.”

    #”ConverteTabela” (quarta linha)

    Resposta
    • 5 de novembro de 2018 em 8:08 PM
      Permalink

      Olá, Eduardo!

      Já peguei o problema!

      Quando colei no blog, o código, ele trocou as aspas duplas.
      Já ajustei o código.

      Pode testar de novo, por gentileza?

      Obrigada pelo toque! 🙂

      Resposta
      • 20 de dezembro de 2018 em 8:50 PM
        Permalink

        Olá. Obrigado pelo ótimo código.
        No meu caso, algumas colunas ficaram em branco quando saio do Editar Consultas e vejo pela visão de dados da tela principal. Colunas essas como “Ano”, “DiaAno”, “SemanaMes”, “Bimestre”, “Semestre” e “TrimestreNum”. Sabe o que pode ser?

        Resposta
        • 5 de janeiro de 2019 em 1:51 AM
          Permalink

          Olá, Karim! Tudo bem?

          Nuss, eu vi hoje quando estava utilizando esse código em um dos meus Pbix.
          Ainda não sei ao certo o que é, mas vou avaliar e, assim que tiver um posicionamento, atualizo o post, ok?

          Obrigada por avisar! 🙂

          Resposta
    • 17 de março de 2019 em 9:47 PM
      Permalink

      Após substituir as aspas em um editor de texto, ele reclama das vírgulas, fiz a substituição também mas não adiantou.

      Expression.SyntaxError: Esperava-se o token Comma.

      Resposta
      • 15 de abril de 2019 em 11:43 PM
        Permalink

        Hum, esse erro é porque deve estar faltando alguma vírgula. Vc conseguiu utilizar o código depois?

        Resposta

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *