BI com Excel: dashboard com Power Query
Trabalhando com dados externos - Apresentação
Boas-vindas! Sou Roberto Sabino, instrutor na Alura. Neste curso, vamos finalizar nosso estudo de Business Intelligence com Excel, aprendendo sobre dashboards e Power Query.
Autodescrição: Sou um homem de pele clara com olhos castanhos. Tenho cabelos e barba escuros, ligeiramente esbranquiçados. Estou vestindo uma camiseta cinza escura.
O que vamos aprender?
Neste curso, vamos conectar dois conceitos: a utilização do Power Query com a criação de dashboards. Esse assunto é muito interessante, pois é muito usado nas empresas atualmente.
Nosso objetivo não é fazer operações altamente complexas. Vamos aprender da forma mais tranquila possível, aos poucos, entendendo cada mecanismo e cada passo da utilização do Power Query e da criação do dashboard.
Descobriremos como buscar dados externos a uma planilha e utilizá-los em um dashboard. Em tese, já sabemos fazer esse processo, mas agora aprenderemos, na prática, como operacionalizá-lo.
Este curso foi desenvolvido para ter uma aprendizagem ativa, então você terá possibilidades de interagir conosco e usar o fórum. Vamos estudar?
Trabalhando com dados externos - Importando dados externos
Chegou o momento de fazer dashboards usando Power Query! Em geral, começamos o curso relembrando o que estudamos anteriormente. Dessa vez, faremos um processo um pouco diferente, porém com uma dinâmica semelhante.
Dados externos
A Clara abriu uma nova filial de sua empresa e seu funcionário enviou o arquivo Vendas.csv, que contém informações separadas pelo símbolo de ponto e vírgula (";"). Portanto, subiremos esses dados externos em uma planilha. Vamos começar abrindo uma nova planilha no Excel.
De costume, clicaríamos sobre a tabela com o botão direito para importar os dados. Contudo, no curso anterior, descobrimos uma alternativa. Ao acessar a aba "Dados" do menu superior, há uma área denominada "Obter e Transformar Dados" no canto esquerdo. Entre outros, temos o botão "Obter Dados" e o botão "De Tabela/Intervalo".
O botão para obter dados de uma tabela ou intervalo é usado quando as informações estão dentro da planilha com a qual pretendemos trabalhar. No nosso caso, queremos trazer dados externos! Essa é a grande diferença entre o processo atual e o que fizemos em cursos anteriores.
O formato CSV é muito utilizado atualmente e podemos trazer os dados desse arquivo diretamente para o Excel. Basta clicar em "Obter Dados > De Arquivo > De Text/CSV". Uma pequena janela será aberta, na qual navegaremos pela estrutura de pastas até o arquivo Vendas.csv. Vamos selecioná-lo e clicar no botão "Importar" no canto inferior direito.
Uma dica: quando trabalhamos com dados, é comum ter diferentes versões de um mesmo arquivo, então é importante confirmar se estamos importando o arquivo correto! Ao subir informações no Excel, teremos uma consulta no Power Query. Se o arquivo original for alterado, os dados obtidos também serão alterados!
Após a importação, aparecerá outra janela com os dados preparados. No canto inferior direito, clicaremos no botão "Transformar Dados" para abrir o editor do Power Query.
Já conhecemos o Power Query, mas vamos recapitular. Na lateral esquerda, temos as consultas feitas (por enquanto, apenas uma). Na parte central, temos os dados organizados. Na lateral direita, temos as propriedades da consulta e as etapas aplicadas até agora. Por ora, temos as seguintes etapas:
Fonte
Cabeçalhos Promovidos
Tipo Alterado
A primeira etapa sempre é "Fonte". Em seguida, o Power Query identificou automaticamente os cabeçalhos obtidos do arquivo CSV. Depois, um tipo foi alterado para estabelecer uma tipologia. No caso, o sistema identificou que os dados da primeira coluna são datas e já definiu o tipo correspondente. O mesmo foi feito na última coluna, que contém valores financeiros. É possível notar essas alterações analisando o símbolo no canto superior esquerdo de cada coluna. Datas têm um símbolo de calendário, valores numéricos têm um símbolo escrito "123", valores financeiros têm o símbolo de cifrão.
No canto superior esquerdo, vamos clicar no botão "Fechar e Carregar". Como padrão, os dados serão exibidos em uma tabela do Excel. Essa consulta é a representação dos dados que estão no arquivo de texto.
Vale reforçar que se trata de uma consulta! Se alterarmos um valor de 5 para 10, ele voltará ao valor 5, quando atualizarmos a tabela. A origem dos dados é o arquivo Vendas.csv, é a partir dele que conseguimos alterar os dados!
Alteração de dados
A seguir, aprenderemos como alterar os dados no arquivo de origem. No entanto, primeiramente, demostraremos um erro comum nesse processo. Vamos abrir o arquivo Vendas.csv no Wordpad. As duas primeiras linhas serão as seguintes:
Data;ID Produto;Quatidade;Preço
03/01/2022;510;5;R$ 30,00
...Alteraremos um valor na segunda linha. A quantidade passará de 5 para 10:
Data;ID Produto;Quatidade;Preço
03/01/2022;510;10;R$ 30,00
...Após salvar o arquivo Vendas.csv, voltaremos ao Excel, atualizaremos a tabela e ocorrerá um erro! Alguns editores de texto (como Word ou WordPad) formatam o arquivo e adicionam caracteres ao formato CSV, de modo a causar esse problema. Para alterar dados, é preciso usar o Bloco de Notas — é o que faremos no próximo vídeo.
Trabalhando com dados externos - Preparando os dados
Agora, usaremos o Bloco de Notas para alterar os dados no arquivo de origem.
No vídeo anterior, nós demonstramos que alterar o arquivo CSV em alguns editores de texto acarreta um erro na atualização da tabela, pois eles formatam o arquivo e teríamos que tratá-lo para que voltasse a funcionar. É importante conhecermos esses erros comuns, para não perdermos tempo no dia a dia.
Alteração com Bloco de Notas
Primeiramente, vamos baixar o arquivo Vendas.csv novamente, para utilizá-lo no formato original, sem a formatação do WordPad. Em seguida, repetiremos o processo de importação dos dados para o Excel. Lembre-se da dica do vídeo anterior: certifique-se de que está importando a versão correta!
Agora, abriremos o arquivo Vendas.csv com o Bloco de Notas. Basta clicar com o botão direito sobre o arquivo e selecionar "Abrir com > Bloco de Notas". Aparentemente, essa ferramenta abre o arquivo igual ao WordPad, porém há uma grande diferença: o Bloco de Notas não muda a codificação do arquivo de texto.
Vamos analisar o formato do arquivo CSV:
Data;ID Produto;Quantidade;Preço
03/01/2022;510;5;R$ 30,00
03/01/2022;516;4;R$ 18,00
04/01/2022;510;7;R$ 42,00
...Na primeira linha, constam os rótulos, separados pelo símbolo de ponto e vírgula (";"). A partir da segunda linha, temos os dados da tabela, também separados por ponto e vírgula, na mesma disposição dos rótulos. Assim, o Excel e o Power Query interpretam que, a cada símbolo de ponto e vírgula, há uma nova informação. Se modificarmos a disposição desses símbolos, podemos causar um problema na leitura.
Na primeira linha da tabela, vamos alterar a quantidade de 5 para 10:
Data;ID Produto;Quantidade;Preço
03/01/2022;510;10;R$ 30,00
...Após salvar o arquivo Vendas.csv, voltaremos à planilha no Excel e atualizaremos a consulta. Agora, o valor na primeira linha da tabela foi modificado de 5 para 10! Ou seja, essa planilha está conectada ao arquivo Vendas.csv — a origem dos dados.
Ao alterar o arquivo de texto, a consulta será alterada. Ao apagar o arquivo de texto, a consulta dará erro. Ao salvar o arquivo em um editor de texto que altera a codificação (como o Word), a consulta também dará erro.
Dashboard de vendas
O mecanismo para usar o Power Query é o mesmo que aprendemos anteriormente. Basta acessar a aba "Consulta" no menu superior e clicar no botão "Editar" no canto esquerdo. À esquerda, temos as consultas; à direita, as propriedades e etapas aplicadas. Agora, podemos partir para as transformações.
A Clara deseja construir o seguinte dashboard de vendas com esses dados:
No canto superior esquerdo, temos o logotipo e nome da empresa da Clara: Serenatto - Café & Bistrô. Na parte superior direita, há três retângulos dispostos horizontalmente, exibindo o faturamento total (R$8.065,50), o maior faturamento (R$836,50) e o mês de maior faturamento (dezembro). No canto inferior esquerdo, há um gráfico circular de faturamento anual, com dados de 2022 e 2023. Na parte inferior direita, há um gráfico de linhas, mostrando o faturamento mensal, de janeiro a dezembro de 2022. Os valores variam entre R$784,50 e R$529,00.
Note que as informações de faturamento mensal estão separadas por ano. No canto superior esquerdo do gráfico de faturamento mensal, é possível selecionar o ano cujos dados queremos analisar.
Enquanto o Power Query está aberto, não é possível mexer no dashboard, apenas visualizá-lo. Para selecionar o ano, por exemplo, é preciso fechar o Power Query.
Há duas maneiras simples de fazer um dashboard. A seguir, vamos explicá-las brevemente e, ao longo do curso, aprenderemos o passo a passo mais detalhadamente.
A primeira maneira é criar uma base, utilizando funções. Atualmente, nossa consulta possui dados de data, ID do produto, quantidade e preço. No entanto, ela não contém diretamente outros dados que precisamos para o dashboard, como faturamento total. Mas nós podemos tratar os dados com funções para obter o que buscamos.
Nessa base, será possível separar os dados no formato desejado, selecionando o ano, o faturamento total, o maior faturamento e o mês de maior faturamento. Além disso, conseguiremos produzir tabelas com o faturamento anual e o faturamento mensal.
A segunda maneira é usando o Power Query para mudar a consulta, de modo que ela contenha as informações necessárias para a montagem do dashboard. Por exemplo, para obter dados mensais e anuais, podemos criar colunas específicas para separar o mês e o ano. No próximo vídeo, vamos fazer esses tratamentos, depois entenderemos como construir o dashboard.
Sobre o curso BI com Excel: dashboard com Power Query
O curso BI com Excel: dashboard com Power Query possui 155 minutos de vídeos, em um total de 44 atividades. Gostou? Conheça nossos outros cursos de Excel em Data Science, ou leia nossos artigos de Data Science.
Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:
- Trabalhando com dados externos
- Dashboard de vendas
- Acrescentando novos dados
- Analisando dashboards
- Recordando a Formação