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);
09/08/2019:
Edit: O código quando colado aqui na página, altera o tipo das aspas. Eu não sei ainda ao certo porque isso ocorre, porém, para evitar problema com o código, segue o caminho com o arquivo.
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
Sensacional Obrigado
Não há de que! É um prazer compartilhar conhecimento! 🙂
Fantástico. Substitui as aspas e funcionou. Valeu…
Top! Conte conosco!
100% Funcional. Obrigado.
let
// Criando Range de Data Entre 01/01/2018 e Hoje
Fonte = #date(2018, 01, 01),
ListaDatas = List.Dates(Fonte,Number.From(DateTime.LocalNow())-Number.From(Fonte),#duration(1,0,0,0)),// caso queira inserir uma data fixa substitutir DateTime.LocalNow() para #date(2019,1,1)
#”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
Parabéns!! Funcionou, somente tive que substituir as aspas, mas fora isso foi 10!!!
let
// Criando Range de Data Entre 01/01/2018 e Hoje
Fonte = #date(2018, 01, 01),
ListaDatas = List.Dates(Fonte,Number.From(DateTime.LocalNow())-Number.From(Fonte),#duration(1,0,0,0)),// caso queira inserir uma data fixa substitutir DateTime.LocalNow() para #date(2019,1,1)
#”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
Não rodou… :/
Deu algum erro?
Se sim, pode postar aqui?
Boa tarde! Fui utilizar do código até funcionou mas sempre quando clico para Atualizar ocorre um erro de atualização.
Segue o erro que ocorre, já tentei decifrar o erro mas sem sucesso.
let
Fonte = DCalendario,
#”Índice Adicionado” = Table.AddIndexColumn(Fonte, “Número da Linha” ,1),
#”Erros Mantidos” = Table.SelectRowsWithErrors(#”Índice Adicionado”, {“Data”, “DiaNum”, “DiaComZero”, “DiaNome”, “DiaAno”, “MesNum”, “MesComZero”, “MesNome”, “MesNomeAbrev”, “MesNomeAbrevAno”, “Mes Extenso”, “Ano”, “Ano/Mes”, “DiaSemana”, “DiaSemanaAbrev”, “DiaSemanaNome”, “SemanaAno”, “Semana, Ano”, “SemanaExtenso”, “SemanaMes”, “Bimestre”, “Semestre”, “TrimestreNum”, “TrimestreNom”, “InicioMes”, “FimMes”, “Calendario Ano”, “FinalSemana”, “FlagHoje”, “FlagMesAtual”, “FlagTrimestreAtual”, “FlagSemanaAtual”, “FlagAnoAtual”, “YTD”}),
#”Colunas Reordenadas” = Table.ReorderColumns(#”Erros Mantidos”, {“Número da Linha”, “Data”, “DiaNum”, “DiaComZero”, “DiaNome”, “DiaAno”, “MesNum”, “MesComZero”, “MesNome”, “MesNomeAbrev”, “MesNomeAbrevAno”, “Mes Extenso”, “Ano”, “Ano/Mes”, “DiaSemana”, “DiaSemanaAbrev”, “DiaSemanaNome”, “SemanaAno”, “Semana, Ano”, “SemanaExtenso”, “SemanaMes”, “Bimestre”, “Semestre”, “TrimestreNum”, “TrimestreNom”, “InicioMes”, “FimMes”, “Calendario Ano”, “FinalSemana”, “FlagHoje”, “FlagMesAtual”, “FlagTrimestreAtual”, “FlagSemanaAtual”, “FlagAnoAtual”, “YTD”})
in
#”Colunas Reordenadas”
Olá, Cleuber!
Tente baixar o arquivo direto do Google Drive!
No blog, as aspas não saem corretamente!
Abs.
Sensacional, consegui e deu certo, parabéns, uma duvida a ultima data vai ser sempre = hoje
Não. Vc pode trocar ou criar alguma condição para ir até a data que vc precisa. 🙂
Sensacional, consegui e deu certo, parabéns, uma duvida a ultima data vai ser sempre = hoje?
Não. Vc pode trocar ou criar alguma condição para ir até a data que vc precisa. ?
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?
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. 🙂
Erro:
“Expression.SyntaxError: Esperava-se o token Identifier.”
#”ConverteTabela” (quarta linha)
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! 🙂
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?
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! 🙂
Post atualizado! Já foi tratado este erro! Ocorria nos campos que eu trocava o tipo de dado de número para texto!
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.
Hum, esse erro é porque deve estar faltando alguma vírgula. Vc conseguiu utilizar o código depois?