Você já precisou trabalhar com horas no Power BI? Teve dificuldades? Leu o título do post e ficou confuso porque sempre te falaram que não era possível?! Então continue aí porque te mostrarei em detalhes como trabalhar com horas no formato hh:mm:ss, como fazer drill down na dCalendario até chegar em segundos e muito mais!
Cenário
Trabalharemos com um Case de Produção – sugerido por um aluno do curso completo. Iremos trabalhar com uma base de dados fictícia bem simples, apenas para mostrar o procedimento e em Outubro…
Acesse esse case e vários outros dashboards tornando-se aluno do Xperiun.
Base de dados
Nossa base de dados consiste de um arquivo em Excel com uma tabela fato chamada fProducao. Ela possui colunas com informações de cada operação realizada por cada operador. Temos duas colunas em formato data/hora com o início e o fim de cada operação.
Restrições iniciais
O maior problema que identifiquei nesse cenário é tentar trabalhar com esses dados de tempo em formato hh:mm:ss. O PBI não permite isso de forma padrão quando ultrapassamos 24h! Precisaremos trabalhar com duração total em horas primeiro, para depois converter para hh:mm:ss no momento de visualizar essas informações!
Dica:
Não é possível fazer soma, média, qualquer tipo de conta com um dado no formato hh:mm:ss no Power BI, por isso precisamos dela no formato de duração inicialmente. Pode ser duração em horas, minutos ou segundos, você quem escolhe. Neste artigo vou considerar o uso da duração em horas.
Repare que o PBI automaticamente modificou os tipos de dados e, dessa vez, ficou tudo certinho. Veja que as colunas DataHoraInicial e DataHoraFinal ficaram no formato dd/mm/aaaa hh:mm:ss, beleza?!
Após checar se está tudo certo com nossa base, clique em Fechar e Aplicar.
Em termos de performance, recomendo sempre que você separe a coluna de Data/Hora em duas – uma coluna de data e outra de hora, porque no formato de data/hora temos mais valores distintos na coluna, e quanto mais valores distintos em uma coluna, pior a performance do modelo, pois isso dificulta a compressão das colunas.
Sem falar no fato que uma coluna data/hora não pode ser relacionada com a dimensão Calendário. Precisamos sempre da data na hora 00:00:00 (meia-noite). Por isso quebramos a coluna data/hora em data e hora separadas.
Reforçando algo muito importante: Quando temos data e hora na mesma coluna o número de combinações possíveis desses dois valores é bem maior – ou seja, teremos mais valores distintos nessa coluna. Sempre tenha isso em mente ao trabalhar no PBI, beleza?!
Dica:
Quanto mais valores distintos tiver uma coluna, menos performático o modelo estará. Recomenda-se dividir a coluna Data/Hora em duas – uma para Data e outra para Hora.
Teste inicial
Lembra da restrição que citamos sobre o PBI não trazer valor de horas corretamente quando ela passa de 24h? Vamos fazer um teste rapidamente para ver o que ocorre, de fato.
Vamos adicionar uma coluna via DAX com as horas decorridas entre a DataHoraInicial e a DataHoraFinal. Adicione uma coluna nova e digite a fórmula a seguir para replicar o exemplo:
Horas decorridas =fProducao[DataHoraFinal] – fProducao[DataHoraInicial]
Após mudar o tipo para o formato Hora, você perceberá que o PBI não mostra valores acima de 24 horas (o mesmo aconteceria no Excel). No lugar ele só apresenta o restante (minutos e segundos).
Repare o resultado dessa fórmula quando o valor passa de 23:59:59:
Se multiplarmos este valor por 24, aí teríamos a quantidade de horas no formato decimal:
Horas decorridas =24 * ( fProducao[DataHoraFinal] – fProducao[DataHoraInicial] )
Nossa estratégia será trabalhar no formato decimal e só no final voltar com o formato hh:mm:ss, beleza?!
Esse foi só um teste! Vamos apagar essa coluna e voltar lá para o Power Query para fazer o que tínhamos mencionado: trabalhar com uma coluna de data e outra de hora – para melhorar a performance do modelo.
Tratamento no Power Query
Primeiro faremos esse mesmo cálculo (subtração) no Power Query. Para isso, siga os passos:
Etapas: 1. Clique com o mouse na coluna DataHoraFinal 2. Clique com o mouse na coluna DataHoraInicial 3. Vá em Adicionar coluna e em Hora, selecione Subtrair
Molezinha, né?! Agora basta transformarmos essa coluna usando esse recurso do Power Query:
Etapas: 1. Clique com o mouse na coluna Subtração 2. Vá em Transformar 3. Clique em Duração 4. Selecione Total de Horas
Vamos renomear essa coluna Subtração para Horas. Você pode editar as etapas anteriores ou adicionar uma nova etapa. Sugiro sempre reduzir o número de etapas aplicadas editando as etapas anteriores para melhorar a performance e tornar o carregamento (e atualização) dos dados mais rápido.
Agora que já temos a coluna com a tempo decorrido (Horas), podemos dividir as colunas DataHoraInicial e DataHoraFinal em duas colunas cada (uma com data e outra com hora), certo?! Faremos isso usando o recurso Dividir Coluna do PQ.
Etapas: 1. Clique com o mouse na coluna DataHoraInicial 2. Vá em Transformar 3. Clique em Dividir Coluna 4. Selecione Por Delimitador 5. Selecione Espaço e Cada Ocorrência do Delimitador
Você notará que o PBI ao dividir essa coluna em duas, automaticamente renomeou para DataHoraInicial.1 e DataHoraInicial.2.
Ficaram feios esses nomes, concorda?! Vamos editar etapas anteriores para renomeá-las:
Veja acima que renomeamos DataHoraInicial.1 para DataInicial e DataHoraInicial.2 para HoraInicial.
Você deve repetir exatamente o mesmo procedimento com a coluna DataHoraFinal, ok?!
Por fim, clique em Fechar e Aplicar.
Clique Aqui para aprender mais sobre Power Query.
Medidas no formato hh:mm:ss
Criaremos nossa primeira medida! Antes disso, vamos inserir uma tabela ’em branco’ apenas para organizarmos nossas medidas ali no painel Campos.
Nossa primeira medida será a soma de Horas.
Repare que ao adicionarmos um visual do tipo Tabela com a coluna Operador e a medida Soma Horas Decimal, teremos o seguinte:
Agora precisamos fazer alguns cálculos para encontrar quantas horas inteiras, minutos e segundos tem essa medida. Para transformar esse valor decimal de horas em minutos basta multiplicarmos a parte decimal inteira (após a vírgula) por 60. Para converter minutos para segundos, precisamos pegar a parte decimal do resultado anterior e multiplicar por 60 novamente, certo? Veja os cálculos que fizemos na calculadora para o valor da primeira linha da Tabela (operador ELVIS):
Faremos uma medida DAX para replicar esse cálculo. Veja que não precisaremos criar várias medidas porque utilizaremos variáveis! Segue o comando completo para facilitar sua vida:
Soma Hora (hh:mm:ss) =VAR vHorasDecimal = [Soma Horas Decimal]
VAR vHoras = INT ( vHorasDecimal )
VAR vMinutosDecimal = 60 * ( vHorasDecimal – vHoras )
VAR vMinutos = INT ( vMinutosDecimal )
VAR vSegundos = ROUND ( 60 * ( vMinutosDecimal – vMinutos ), 0 )
VAR vResultado = vHoras & “:” & vMinutos & “:” & vSegundos
RETURN
vResultado
Veja que conseguimos trazer de volta o formato hh:mm:ss via DAX:
Perceba que se tentarmos inserir essa medida no eixo de algum gráfico padrão do Power BI, não conseguiremos. Mas você pode usar essa medida como tooltip:
Como nosso total de horas está em decimal, será tranquilo calcular a média, veja:
Media Horas Decimal = AVERAGE ( fProducao[Horas] )
Para calcular a média no formato hh:mm:ss, basta você copiar o código da medida Soma horas (hh:mm:ss) e alterar a variável vHorasDecimal para utilizar a medida Média Horas Decimal no lugar de Soma Horas Decimal.
Vamos ver como ficaram essas duas novas medidas na tabela?
Vocês sabem que tenho T.O.C., né?! Preciso arrumar essa última medida que criamos para deixarmos com dois dígitos as horas, minutos e segundos que aparecem ali.
VAR vHorasDecimal = [Media Horas Decimal]
VAR vHoras =
INT ( vHorasDecimal )
VAR vMinutosDecimal = 60 * ( vHorasDecimal – vHoras )
VAR vMinutos =
INT ( vMinutosDecimal )
VAR vSegundos =
ROUND (
60 * ( vMinutosDecimal – vMinutos ),
0
)
VAR vHH =
IF (
LEN ( vHoras ) = 1,
“0” & vHoras,
vHoras
)
VAR vMM =
IF (
LEN ( vMinutos ) = 1,
“0” & vMinutos,
vMinutos
)
VAR vSS =
IF (
LEN ( vSegundos ) = 1,
“0” & vSegundos,
vSegundos
)
VAR vResultado = vHH & “:” & vMM & “:” & vSS
RETURN
vResultado
Vamos aproveitar e arrumar também a medida Soma Hora (hh:mm:ss):
VAR vHorasDecimal = [Soma Horas Decimal]
VAR vHoras = INT ( vHorasDecimal )
VAR vMinutosDecimal = 60 * ( vHorasDecimal – vHoras )
VAR vMinutos INT ( vMinutosDecimal )
VAR vSegundos = ROUND ( 60 * ( vMinutosDecimal – vMinutos ), 0 )
VAR vHH = IF ( LEN ( vHoras ) = 1, “0” & vHoras, vHoras )
VAR vMM = IF ( LEN ( vMinutos ) = 1, “0” & vMinutos, vMinutos )
VAR vSS = IF ( LEN ( vSegundos ) = 1, “0” & vSegundos, vSegundos )
VAR vResultado = vHH & “:” & vMM & “:” & vSS
RETURN
vResultado
Clique Aqui para se tornar um expert em DAX.
Drill down em segundos
A próxima tarefa será criar uma dCalendario e conseguirmos chegar até o nível de segundos no drill down!
Tenha em mente que a tabela dCalendario SEMPRE deve estar na granularidade de DATA, nunca de hora!
Essa tarefa parece complexa mas não é! Para relacionar as horas da tabela fato você vai precisar criar uma tabela dimensão de hora (dHora).
A primeira coisa a fazer é construir nossa dCalendario. Faremos via DAX, veja:
dCalendario = CALENDAR ( DATE ( 2020, 01, 01 ), DATE ( 2020, 12, 31 ) )Com a dCalendario criada, precisaremos relacioná-la com nossa tabela fato. A escolha da coluna DataInicial ou DataFinal dependerá do seu modelo – o que você considerará mais importante. Escolhi DataInicial da tabela fProducao para relacioná-la a coluna Date (da tabela dCalendario), veja:
Vamos precisar voltar no Power Query para criar a tabela dHora. Primeiro crie uma Consulta nula e depois vá em Editor Avançado para colar o código que vou disponibilizar logo abaixo:
Código M para copiar e colar no Editor Avançado:
SecList = {0 .. 86399},
SecTable = Table.FromList(
SecList,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
SecType = Table.TransformColumnTypes(
SecTable,
{{“Column1”, Int64.Type}}
),
SecName = Table.RenameColumns(
SecType,
{{“Column1”, “Second”}}
),
TimeDurCol = Table.AddColumn(
SecName,
“TimeDuration”,
each #duration(0, 0, 0, [Second]),
type duration
),
TimeCol = Table.AddColumn(
TimeDurCol,
“Time”,
each #time(
Duration.Hours([TimeDuration]),
Duration.Minutes([TimeDuration]),
Duration.Seconds([TimeDuration])
),
type time
),
HourCol = Table.AddColumn(
TimeCol,
“Hour Number”,
each Time.Hour([Time]),
Int64.Type
),
MinuteCol = Table.AddColumn(
HourCol,
“Minute Number”,
each Time.Minute([Time]),
Int64.Type
),
SecondCol = Table.AddColumn(
MinuteCol,
“Second Number”,
each Time.Second([Time]),
Int64.Type
),
HourMinuteCol = Table.AddColumn(
SecondCol,
“Hour Minute”,
each #time([Hour Number], [Minute Number], 0),
type time
&nbs p;),
AMPMCol = Table.AddColumn(
HourMinuteCol,
“AMPM”,
each if [Second] < 43200 then “AM” else “PM”,
type text
),
HourNmCol = Table.AddColumn(
AMPMCol,
“Hour”,
each
if [Hour Number] = 0 then
“12 AM”
else if [Hour Number] >= 1 and [Hour Number] <= 11 then
Number.ToText([Hour Number]) & ” AM”
else if [Hour Number] = 12 then
“12 PM”
else if [Hour Number] >= 13 then
Number.ToText([Hour Number] – 12) & ” PM”
else
“Unknown”,
type text
),
HourlyQuartileCol = Table.AddColumn(
HourNmCol,
“Hourly Quartile”,
each
if [Hour Number] >= 0 and [Hour Number] <= 5 then
“12AM to 6AM”
else if [Hour Number] >= 6 and [Hour Number] <= 11 then
“6AM to 12PM”
else if [Hour Number]
>= 12 and [Hour Number]
<= 17
then
“12PM to 6PM”
else if [Hour Number]
>= 18 and [Hour Number]
<= 23
then
“6PM to 12AM”
else
“Unknown”,
type text
),
HourlyQuartileSortCol = Table.AddColumn(
HourlyQuartileCol,
“Hourly Quartile Sort”,
each
if [Hour Number] >= 0 and [Hour Number] <= 5 then
1
else if [Hour Number] >= 6 and [Hour Number] <= 11 then
2
else if [Hour Number]
>= 12 and [Hour Number]
<= 17
then
3
else if [Hour Number]
>= 18 and [Hour Number]
<= 23
then
4
else
5,
Int64.Type
),
RemoveTimeDurationCol = Table.RemoveColumns(
HourlyQuartileSortCol,
{“TimeDuration”}
)
in
RemoveTimeDurationCol
A referência original desse código pode ser encontrada clicando aqui.
Após renomear a Consulta1 para dHora, nossa tabela estará assim:
Perceba que teremos 1 nova linha para cada segundo da coluna Hora. Ou seja, se multiplicarmos 60 (seg) x 60 (min) x 24 (h) teremos o total de 86400 linhas, ok?!
Clique em Fechar e Aplicar e relacione a tabela dHora com a tabela fProducao:
Vamos ver como ficará o Drill down no gráfico de área ? Veja como fizemos para chegar até o nível AMPM:
Continuando até o detalhe em segundos:
Dica Bônus
Durante a Live #28 eu tinha comentado ao vivo que NÃO tinha como adicionar o formato hh:mm:ss num gráfico sem ser via tooltip. Mas o nosso colega Alfonso Haskel nos salvou!
Ah, vou aproveitar e indicar esse vídeo que mostra como utilizar outros formatos de número personalizados no Power BI – como por exemplo aquele parênteses para números negativos que o pessoal de finanças e contábeis costuma usar.
Bom, vamos à solução! A primeira coisa que faremos é concatenar as horas, minutos e segundos e depois converter ir resultado para Inteiro usando a função CONVERT diretamente naquela medida Soma Hora (hh:mm:ss):
Soma Hora (hh:mm:ss) =VAR vHorasDecimal = [Soma Horas Decimal]
VAR vHoras = INT ( vHorasDecimal )
VAR vMinutosDecimal = 60 * ( vHorasDecimal – vHoras )
VAR vMinutos = INT ( vMinutosDecimal )
VAR vSegundos = ROUND ( 60 * ( vMinutosDecimal – vMinutos ), 0 )
VAR vHH = IF ( LEN ( vHoras ) = 1, “0” & vHoras, vHoras )
VAR vMM = IF ( LEN ( vMinutos ) = 1, “0” & vMinutos, vMinutos )
VAR vSS = IF ( LEN ( vSegundos ) = 1, “0” & vSegundos, vSegundos )
RETURN
CONVERT ( vHH & vMM & vSS, INTEGER )
Agora, basta mudarmos o formato dessa medida digitando em Formato “00:00:00”, veja:
Preparados para ver como ficará essa medida num gráfico?
Para que exiba corretamente o formato que indicamos basta ir em Exibir unidades (em Rótulos – Painel Visualizações) e selecionar Nenhum, beleza? Deve-se fazer o mesmo no Eixo Y:
Clique Aqui para adquirir um dashboard completo de PCP (produção).
Bom, fechou agora!
Espero que tenha gostado do conteúdo.
Um abraço,
Leonardo.
