Fala galera! Tudo bem!??
Hoje o post é referente ao conteúdo da Live#2 do Workshop #1. Vamos montar um Dashboard de Estoque com Análise de Saldo e Custo. O principal desafio nele é o que iremos trabalhar no Power Query.
O relatório vai conter os seguintes visuais:
- Análise de custo e quantidade ao longo do tempo
- Análise de custo por tipo de produto
- Análise de custo e quantidade por armazém
- Análise de custo por família e grupo
- Filtros: temporais, por tipo de produto e por armazém
Clique Aqui para se tornar aluno do curso mais completo de Power BI do mundo, e tenha acesso a esse e vários outros dashboards.
Base de dados
Nossa base é composta por 4 arquivos:
- Armazem: tabela dimensão com os cadastros dos armazéns da empresa
- Estoque2018: tabela fato com as ocorrências do estoque de 2018
- Estoque2019: tabela fato com as ocorrências do estoque de 2019
- Produtos: tabela dimensão com os cadastros de produtos da empresa
Figura 1: Arquivos base de dados
Figura 2: Arquivo Armazem
Figura 3: Arquivo Estoque2018
Figura 4: Arquivo Estoque2019
Figura 5: Arquivo Produtos
ETL
Para importar os arquivos no Power Query iremos utilizar um artificio de conexão direto via pasta:
Figura 6: Obter dados de Pasta
Figura 7: Seleção da Pasta
Figura 8: Seleção de transformação de dados
Etapas: 1. Em "Página inicial" clicar em "Nova fonte" → Clicar em "Pasta" 2. Colar o caminho da pasta dos arquivos no campo 3. Clicar em "Transformar Dados"
O resultado dessa importação vai servir como a base de dados para nosso projeto:
Figura 9: Alteração do Nome da Consulta
Nossas tabelas fato e dimensão serão referenciadas a partir dessa consulta como se fossem ramificações da mesma:
Figura 10: Criação das tabelas fato e dimensão
Etapas: 1. Clicar com o botão direito na consulta "BaseDados" → Clicar em "Referência" (3x) 2. Renomear as novas consultas, para: dArmazem, dProduto e fEstoque
Após referenciar as novas consultas, podemos ver as conexões em “Dependência de Consulta” e os parâmetros para configurar gateway em “Configurações da fonte de dados”:
Figura 11: Exibição das dependências de consultas
Figura 12: Configurações da fonte de dados
dArmazem
A partir da referência para a dArmazem, vamos exibir seu conteúdo clicando em Binary:
Figura 13: Expansão do conteúdo de Armazem.csv
Etapas: Selecionar a consulta "dArmazem" e clicar em "Binary" da linha de "Armazem.csv"
Figura 14: Resultado da Expansão
Veja que precisamos fazer um tratamento para retirar a linha em branco, retirar a linha de “Cadastro de Armazém” e nomear nossas colunas. Para isso, vamos fazer os seguintes passos:
Figura 15: Removendo linhas em vazio
Figura 16: Removendo linha “Cadastro de Armazéns”
Figura 17: Promovendo cabeçalho
Etapas: 1. Clicar na seta para baixo ao lado de "Column 1" → Clicar em "Remover Vazio" 2. Clicar na seta para baixo ao lado de "Column 1" → Desmarcar "Cadastro de Armazéns" 3. Clicar no painel a esquerda da "Column 1" → Clicar em "Usar a Primeira Linha como Cabeçalho"
Figura 18: Resultado final da dArmazem
dProduto
Você lembra da estrutura do arquivo de Produtos!?? Nele tínhamos diversas planilhas com os cadastros por tipo de produto fornecido (serviço, produto intermediário, produto acabado, embalagem, etc…). Então, além de tratar valores duplicados para cabeçalhos, pois cada planilha temos essa informação repetida, temos que criar uma coluna para identificar o tipo de produto fornecido.
O primeiro passo é expandirmos o arquivo:
Figura 19: Expansão do conteúdo de Produtos.xlsx
Figura 20: Selecionando colunas
Figura 21: Expandido as Tabelas
Etapas: 1. Selecionar a consulta "dProduto" → Clicar em "Binary" 2. Selecionar a coluna "Data" → Em "Página Inicial" clicar em "Remover Colunas" → Clicar em "Remover Outras Colunas" 3. Clicar no ícone de expansão ao lado no canto superior direito da coluna "Data" → Desmarcar a opção de usar o nome da coluna original como prefixo" → "Ok"
Figura 22: Resultado final da expansão
Com isso, vamos adicionar uma nova coluna para o atributo de tipo de produto:
Figura 23: Adicionando coluna condicional
Figura 24: Preenchimento das linhas
Etapas: 1. Em "Adicionar Coluna" clicar em "Coluna Condicional" 2. Utilizar a lógica descrita 3. Clicar com o botão direito na nova coluna → Clicar em "Preenchimento" → Clicar em "Para Baixo"
Com a nova coluna criada, podemos remover a informação do tipo de produto da primeira coluna utilizando um filtro:
Figura 25: Filtro de texto
Figura 26: Especificação do filtro
Etapas: 1. Clicar na seta no canto superior direito de Column1 → Clicar em "Filtro de Texto" → Clicar em "Não começa com..." 2. Preencher o campo com "TIPO DE PRODUTO"
Então, criamos nosso cabeçalho com a primeira linha:
Figura 27: Criação do cabeçalho
Figura 28: Alteração do cabeçalho para a coluna adicionada
Etapas: 1. Clicar no ícone de tabela no canto superior esquerdo da "Column 1" → Clicar em "Usar a Primeira Linha como cabeçalho" 2. Renomear a coluna "Personalizar" para "Tipo Produto"
Veja que temos um texto desnecessário “TIPO DE PRODUTO: ” em todas as linhas dessa coluna. Vamos remover utilizando “Extrair” para deixar a informação mais limpa:
Figura 29: Extração de texto após delimitador
Figura 30: Filtro dos cabeçalhos duplicados
Etapas: 1. Com a coluna "TipoProduto" selecionada clicar em Transformar" → Clicar em "Extrair" → Clicar em "Texto Após o Delimitador" → Preencher o campo 2. Clicar na seta no canto superior direito da coluna "CodigoProduto" → Desmarcar "CodigoProduto"
O último passo é verificar se todas as colunas estão com os tipos de dados definidos. Se você reparar bem, irá ver que nossa coluna de custo unitário não está com o tipo definido (ver o ícone abaixo):
Figura 31: Definindo tipo de dados
Esse símbolo com ABC/123 nos informa que os dados dessa coluna não estão definidos. O formato correto é de número decimal :
Etapas: Clicar no ícone ABC/123 da coluna "CustoUnit" → definir como "Número Decimal"
| Importante: Sempre que finalizar o tratamento das suas tabelas no Power Query, verifique se todas as colunas estão com o tipo de dados definido corretamente. Isso com certeza te ajudará a não ter problemas nos relacionamentos dos modelos e cálculos. |
Figura 32: Resultado final da dProduto
fEstoque
Para nossas tabelas fato fEstoque temos um passo extra para realizar. Perceba que são duas tabelas (2018 e 2019)! Vamos combinar as duas em uma única com auxilio da função Excel.Workbook:
Figura 33: Arquivos que serão combinados
Figura 34: Filtro de arquivos
Figura 35: Adicionando coluna com Excel.Workbook
Etapas: 1. Clicar na seta no canto superior direito da coluna "Name" → Clicar em "Filtros de Texto" → Clicar em "Começa com..." → Preencher com Estoque 2. Em "Adicionar Coluna" clicar em "Coluna Personalizada" → Preencher com a Fórmula
Figura 36: Resultado da coluna adicionada
Para deixar a tabela mais limpa, vamos remover todas as colunas exceto a “Name” e “Conteudo”:
Figura 37: Removendo colunas
Etapas: Com as colunas "Name" e "Conteudo" selecionadas → Em "Página Inicial" selecionar "Remover Colunas" → Clicar em "Remover Outras Colunas"
Com isso, vamos expandir as informações de “Conteudo”:
Figura 38: Expandindo “Conteudo“
Etapas: Clicar na seta no canto superior direito da coluna "Conteudo" → "OK"
Novamente, iremos aplicar um filtro e remover outras colunas para deixar a tabela limpa:
Figura 39: Filtro somente de tabelas
Figura 40: Removendo outras colunas
Figura 41: Expandindo dados
Etapas: 1. Clicar na seta no canto superior direito da coluna "Kind" → Filtrar "Table" 2. Com as colunas "Name" e "Data" selecionadas → Em "Página Inicial" selecionar "Remover Colunas" → Clicar em "Remover Outras Colunas" 3. Clicar na seta no canto superior direito da coluna "Data" → "OK"
Figura 42: Resultado da expansão da tabela
Aqui é interessante analisar que os meses estão distribuídos em colunas. Essa não é uma boa forma para fazer os cálculos e relacionamentos no Power BI. Além disso, temos que juntar essa informação com o ano que iremos extrair da coluna “Name”. Para isso, vamos fazer o seguinte:
Figura 43: Transformar as colunas de meses em linhas
Figura 44: Extraindo a informação de Ano da coluna Name
Figura 45: Extraindo a informação do Mês da coluna Atributo
Figura 46: União entre as colunas
Figura 47: Detecção do tipo de dado
Figura 48: Remoção de Vazio
Figura 49: Alteração do Nome
Etapas: 1. Selecionar as colunas Name, CodigoProduto e CodigoArmazem → Clicar com o botão direito do mouse em uma das colunas → Clicar em "Transformar outras colunas em linhas" 2. Selecionar a coluna Name → Clicar em "Transformar" → Clicar em "Extrair" → Clicar em "Texto Entre Delimitadores" → Preencher os campos 3. Selecionar a coluna Atributo → Clicar em "Transformar" → Clicar em "Extrair" → Clicar em "Texto Entre Delimitadores" → Preencher os campos 4. Selecionar a coluna Atributo e depois a coluna Name → Clicar com o botão direito no mouse em uma das colunas → Clicar em "Mesclar colunas" → Preencher campos 5. Selecionar todas as colunas → Clicar em Transformar e selecionar "Detectar Tipo de Dados" 6. Selecionar coluna Valor → Remover Vazio 7. Renomear coluna Valor
Clique Aqui para aprender mais sobre Power Query.
dCalendario
Nossa tabela dCalendario será copiada da que criamos para a Live#1 do Workshop.
Não se lembra como foi feita? Calma que é só olhar o post da Live#1 e ver o passo a passo: clique aqui
Figura 50: Copiar a consulta da dCalendario da Live#1
Figura 51: Colar a consulta no Power Query
Etapas: 1. Alterar para List.Dates na Fonte da consulta dCalendario do arquivo da Live 1 2. Copiar a consulta 3. Colar a consulta
Como o inicio da nossa avaliação é em 2018 e não 2017 e o período é de 2 anos (730 dias), vamos alterar esses parâmetros na fonte da dCalendario:
Figura 52: Alteração de inicio e dias do período
Etapas: Na consulta dCalendario, em "Fonte" alterar a data de inicio para 2018 e a contagem de dias para 730
Por fim, iremos desabilitar a carga da consulta de BancoDados, pois não será utilizado nas análises do Power BI:
Figura 53: Desabilitando carga da consulta
Podemos “Fechar e aplicar” as consultas do Power Query e salvar o arquivo.
Relacionamentos
O primeiro passo após salvar o arquivo é verificar os relacionamentos entre as tabelas fato e dimensão:
Figura 54: Verificação de relacionamentos
Veja a importância desse passo na construção do projeto. O Power BI acabou não fazendo o relacionamento entre nossa tabela dCalendario com a tabela fEstoque. Se não tivéssemos verificado o relacionamento, qualquer análise que fizessemos baseada entre as duas tabelas ficaria comprometido. Bora arrumar!???
Figura 55: Criação do relacionamento
Etapas: Arrastar a coluna "Data" da "dCalendario" em cima da coluna "DataEstoque" da "fEstoque"
Cálculos e Visuais
Antes de fazer as medidas, é muito importante pensarmos em como o valor de estoque se comporta. É possível e podemos somar o estoque de bananas com laranja, por exemplo. Então, o estoque é uma medida que podemos fazer adição. Porém, não podemos somar o estoque de ontem com o de hoje. Isso quer dizer que temos que definir a data (ou período) de avaliação.
Para melhor entendimento vamos criar um gráfico do estoque ao longo do tempo e um cartão com a medida de quantidade de estoque:
Fórmula DAX:Qtd Estoque =
SUM ( fEstoque[Saldo] )
Figura 56: Classificando a coluna “Nome do Mês”
Figura 57: Gráfico do estoque ao longo do tempo
Figura 58: Cartão da quantidade de estoque
Etapas: 1. Criar a medida 2. Classificar a coluna de Nome do Mês 3. Criar gráfico de colunas empilhadas → Atribuir os campos conforme imagem 4. Criar cartão → Atribuir os campos conforme imagem
Veja que o valor no cartão é muito alto, ele é a soma de todo o estoque no período inteiro. E sabemos que isso não corresponde a quantidade atual. Para avaliarmos o contexto correto de estoque usaremos o auxilio da CALCULATE e da LASTDATE:
Qtd Estoque =CALCULATE ( SUM ( fEstoque[Saldo] ); LASTDATE ( fEstoque[DataEstoque] ) )
Com isso, o contexto do período de avaliação fica definido e não aberto para qualquer análise que fizermos.
Antes de montar os visuais, vamos importar o background da página e o tema do Dashboard:
Figura 59: Importando segundo plano de página
Figura 60: Importando tema
Etapas: 1. Em "Segundo plano de página" clicar em "Adicionar imagem" → Selecionar o arquivo → Alterar transparência e Ajuste 2. Em "Exibição" clicar em "Procurar temas" → Selecionar Arquivo
O arquivo que importamos (Tema Urban.json) pode ser aberto no Notepad para você visualizar as definições da paleta de cores para cada atributo:
Figura 61: Tema Urban.json
A primeira formatação que iremos fazer será no gráfico de colunas:
Figura 62: Gráfico de colunas empilhadas formatado
Formatação: Eixo X → Tamanho do texto: 10 pt; Cor: E6E6E6 Eixo Y → Desativado Rotulo de dados → Tamanho do texto: 10 pt; Cor:E6E6E6 Título → Tamanho do texto: 12 pt; Cor:FFFFFF; Família de fontes: Segoe (Bold) Tela de Fundo → Transparência: 0 %; Cor:333333
Para a segmentação de dados por Ano, vamos importar um visual personalizado do marketplace (fora dos padrões):
Figura 63: Obter novos visuais
Figura 64: Escolhendo o visual
Etapas: Clicar nos 3 pontos no canto inferior direito de Visualizações → Clicar em Obter mais visuais → Pesquisar por "Chiclets" → Selecionar o visual de Chiclet Slicer
Figura 65: Filtro de Ano
Formatação: Cabeçalho → Desativado Chiclets → Tamanho de texto: 11 pt; Altura: 35 e Largura: 165 Tela de fundo → Transparência: 0 %; Cor:333333
Os outros dois filtros na nossa página são de tipo de produto e de armazém:
Figura 66: Filtro de Tipo de Produto
Formatação: Itens → Cor da fonte: FFFFFF Cabeçalho de segmentação → Cor da fonte: FFFFFF; Tamanho do Texto: 11 pt Tela de fundo → Transparência: 0 %; Cor:333333
Para o visual de filtro de armazéns só precisamos copiar o visual que acabamos de criar e alterar o campo para Armazem:
Figura 67: Filtro de Armazem
Formatação: Copiar o visual de filtro de TipoProduto e alterar o campo para Armazem
Também iremos aproveitar a formatação do primeiro gráfico que montamos para os outros visuais. Então, vamos copiar e colar 3 gráficos na nossa página:
Figura 68: Visuais copiados
A próxima medida que iremos utilizar é a de custo. Para isso, temos que calcular a quantidade do estoque multiplicado pelo seu custo unitário. Para fazer essa relação entre as tabelas usamos o auxilio da função RELATED. Além disso, temos que usar a LASTDATE para não termos o problema de contexto do período de avaliação mencionado anteriormente:
Custo Estoque =CALCULATE (
SUMX ( fEstoque; fEstoque[Saldo] * RELATED ( dProduto[CustoUnit] ) );
LASTDATE ( fEstoque[DataEstoque] )
)
Formato: Moeda, 2 casas decimais
A importância da SUMX com a RELATED é que corremos linha a linha essa relação do custo do produto com seu estoque! Vamos criar um cartão de linha múltipla (com a formatação copiada do gráfico) e colocar as medidas de custo e quantidade de estoque.
Figura 69: Cartão de linhas múltipas
Formatação: Rótulo de dados → Cor da fonte: FFFFFF, Tamanho de texto: 12 pt; Família de fontes: Segoe (Bold) Rótulo de categoria → Família de fontes: Segoe (Bold)
O próximo visual que vamos criar é para analisar o custo de estoque por tipo de produto. Então, vamos alterar um dos gráfico copiados para Gráfico de barras empilhado:
Figura 70: Gráfico de barra empilhado
Etapas: Alterar o tipo de gráfico para "Gráfico de barras empilhados" → Atribuir os campos conforme imagem
O próximo gráfico copiado que iremos alterar será para o formato Treemap, para analisarmos a quantidade de estoque em cada armazém:
Figura 71: Treemap
Etapas: Alterar o tipo de gráfico para "Treemap" → Atribuir os campos conforme imagem
O último gráfico que vamos alterar será para a análise de custo de estoque por produto (família e tipo):
Figura 72: Gráfico de barras empilhadas
Etapas: Alterar o tipo de gráfico para "Gráfico de barras empilhadas" → Atribuir os campos conforme imagem
Nesse momento, o visual da nossa página está assim:
Figura 73: Visual geral da página
Pense como se você fosse o usuário final. O que acha do gráfico de custo de estoque por família e grupo!?? Está fácil de interpretar? Normalmente, o usuário quer enxergar os produtos acabados nesse tipo de gráfico. Para isso, podemos alterar o filtro lateral e deixar o produto acabado selecionado. Vamos ver o que acontece:
Figura 74: Filtro aplicado
O visual que queríamos melhorar, ficou bom! Porém, o visual para analisar o custo de estoque por tipo de produto ficou inútil. Podemos editar a interação para que esse visual em específico não seja filtrado:
Figura 75: Editando interações entre visuais
Etapas: Selecionar o visual de segmentação → Clicar em "Formato" → Clicar em "Editar Interações" → Desabilitar no visual desejado
Vamos inserir dois ícones um para o custo e o outro para a quantidade no cartão:
Figura 76: Inserindo ícones
E também dar um título para nosso Dashboard:
Figura 77: Inserindo caixa de texto
Para os 3 gráficos vamos adicionar uma dica de ferramenta a fim de ajudar o usuário final em suas análises:
Figura 78: Inserindo dicas de ferramenta
Pronto!!! Com isso temos todos os elementos do nosso Dashboard, que ficou assim:
Figura 79: Dashboard Final
Para assegurar que o relatório vai atualizar corretamente caso a gente faça alguma alteração no nosso banco de dados vamos simular que deletamos uma das planilhas de cadastro de produtos e atualizar nosso projeto:
Figura 80: Cópia do arquivo original de produtos
Figura 81: Excluindo produtos de embalagem
Figura 82: Atualizando projeto
Figura 83: Resultado antes da atualização
Figura 84: Resultado após da atualização (sem Material de Embalagem)
Bom galera, esse foi nosso conteúdo da Live#2 do Workshop #1!!!! Conteúdo super especial desse Workshop que fiz com muito carinho e dedicação para vocês. Espero que tenham aprendido vários conceitos de ETL, DAX e criação de visuais para análise de estoques.
Se tiver alguma dúvida ou sugestão para conteúdo das próximas Lives, deixa aqui um comentário.
Grande abraço,
Leonardo!
