Carregar Dados Transformados no Power BI Desktop

História do laboratório

Neste laboratório, você usará técnicas de limpeza e transformação de dados para começar a moldar seu modelo de dados. Em seguida, você aplicará as consultas para carregar cada uma delas como uma tabela no modelo de dados.

Neste laboratório, você aprenderá a:

  • Aplicar várias transformações
  • Carregar consultas no modelo de dados

Este laboratório levará aproximadamente 45 minutos.

Introdução

Para concluir este exercício, primeiro abra um navegador da Web e insira a seguinte URL para baixar a pasta zip:

https://github.com/MicrosoftLearning/PL-300-Microsoft-Power-BI-Data-Analyst/raw/Main/Allfiles/Labs/02-load-data-with-power-query-in-power-bi-desktop/02-load-data.zip

Extraia a pasta para a pasta C:\Usuários\Estudante\Downloads\02-load-data.

Abra o arquivo 02-Starter-Sales Analysis.pbix.

Observação: você pode ignorar a entrada clicando em Cancelar. Feche todas as janelas informativas. Caso precise aplicar as alterações, clique em Aplicar depois.

Configurar a consulta Vendedor

Nesta tarefa, você vai usar o Editor do Power Query para configurar a consulta Vendedor.

Importante: quando instruído a renomear as colunas, será importante que as renomeie exatamente conforme descrito.

  1. Para abrir a janela do Editor do Power Query, na guia de faixa de opções Página Inicial, no grupo Consultas, selecione o ícone Transformar Dados.

    Transformar Dados na faixa de opções Página Inicial

  2. Na janela do Editor do Power Query, no painel Consultas, selecione a consulta DimEmployee.

    Figura 1

    Observação: se você receber uma mensagem de aviso solicitando a especificação de como se conectar, selecione Editar credenciais, conecte-se usando as credenciais atuais e selecione OK para usar uma conexão não criptografada.

  3. Para renomear a consulta, no painel Configurações de Consulta (localizado à direita), na caixa Nome, substitua o texto por Vendedor e clique em ENTER. Em seguida, verifique se o nome foi atualizado no painel Consultas.

    O nome da consulta determina o nome da tabela do modelo. Recomenda-se definir nomes concisos e fáceis de usar.

  4. Para localizar uma coluna específica, na guia de faixa de opções Página Inicial, no grupo Gerenciar Colunas, escolha a seta para baixo Escolher Colunas e selecione Ir para Coluna.

    Ir para Coluna é um recurso útil com muitas colunas. Caso contrário, você pode rolar horizontalmente encontrar colunas.

    Gerenciar colunas > Escolher colunas > Ir para coluna

  5. Na janela Ir para Coluna, para ordenar a lista pelo nome da coluna, selecione o botão de classificação AZ, depois selecione Nome e SalesPersonFlag. Clique em OK.

    Ir para opções de classificação de coluna

  6. Localize a coluna SalesPersonFlag e, em seguida, filtre a coluna para selecionar apenas Vendedores (ou seja, TRUE) e clique em OK.

  7. No painel Configurações de Consulta, na lista Etapas Aplicadas, observe a adição da etapa Linhas Filtradas.

    Cada transformação criada resulta em outra lógica de etapa. É possível editar ou excluir etapas. Também é possível selecionar uma etapa para visualizar os resultados da consulta nesse estágio da transformação da consulta.

    Etapas aplicadas

  8. Para remover colunas, na guia de faixa de opções Página Inicial, no grupo Gerenciar Colunas, selecione o ícone Escolher Colunas.

  9. Na janela Escolher Colunas, para desmarcar todas as colunas, desmarque o item (Selecionar Todas as Colunas).

  10. Para incluir colunas, marque as seis seguintes colunas:

    • EmployeeKey
    • EmployeeNationalIDAlternateKey
    • Nome
    • LastName
    • Título
    • EmailAddress

  11. Na lista Etapas Aplicadas, observe a adição de outra etapa de consulta.

    Etapa de outras colunas removidas

  12. Para criar uma coluna de nome único, primeiro selecione o cabeçalho da coluna FirstName. Ao selecionar a tecla CTRL, selecione a coluna LastName.

    Seleção múltipla de duas colunas para criar uma única coluna

  13. Clique com o botão direito do mouse em um dos cabeçalhos da coluna selecionados e, no menu de contexto, escolha Mesclar Colunas.

    Muitas transformações comuns podem ser aplicadas clicando com o botão direito do mouse no cabeçalho da coluna e escolhendo-as no menu de contexto. No entanto, observe que há mais transformações disponíveis nesta faixa de opções.

  14. Na janela Mesclar Colunas, na lista suspensa Separador, selecione Espaço.

  15. Na caixa Nome da Nova Coluna, substitua o texto por Salesperson.

  16. Para renomear a coluna EmployeeNationalIDAlternateKey, clique duas vezes no cabeçalho da coluna EmployeeNationalIDAlternateKey e substitua o texto por EmployeeID e pressione Enter.

  17. Use as etapas anteriores para renomear a coluna EmailAddress como UPN.

    UPN é um acrônimo de Nome Principal do Usuário.

No canto inferior esquerdo, na barra de status, confirme se a consulta tem cinco colunas e 18 linhas.

Configurar a consulta SalespersonRegion

Nesta tarefa, você vai configurar a consulta SalespersonRegion.

  1. No painel Consultas, selecione a consulta DimEmployeeSalesTerritory.

  2. No painel Configurações de Consulta, renomeie a consulta como SalespersonRegion.

  3. Para remover as duas últimas colunas, primeiro, selecione o cabeçalho da coluna DimEmployee.

  4. Selecionando a tecla CTRL, selecione o cabeçalho da coluna DimSalesTerritory.

  5. Clique com o botão direito do mouse em um dos cabeçalhos da coluna selecionados e, no menu de contexto, selecione Remover Colunas.

Na barra de status, confirme se a consulta tem duas colunas e 39 linhas.

Configurar a consulta de Produto

Nesta tarefa, você vai configurar a consulta Product.

Importante: quando as instruções detalhadas já tiverem sido fornecidas, as etapas do laboratório fornecerão instruções mais concisas. Caso precise das instruções detalhadas, veja novamente as etapas das tarefas anteriores.

  1. Selecione a consulta DimProduct e renomeie a consulta como Produto.

  2. Localize a coluna FinishedGoodsFlag e filtre-a para recuperar os produtos que são mercadorias concluídas (ou seja, TRUE).

  3. Remova todas as colunas, exceto as seguintes:

    • ProductKey
    • EnglishProductName
    • StandardCost
    • Cor
    • DimProductSubcategory

  4. Observe que a coluna DimProductSubcategory representa uma tabela relacionada (ela contém links Valor).

  5. No cabeçalho da coluna DimProductSubcategory, à direita do nome da coluna, selecione o botão Expandir.

    Ícone de expansão de coluna

  6. Consulte a lista completa de colunas e selecione a caixa Selecionar Todas as Colunas para desmarcar todas as colunas.

  7. Selecione EnglishProductSubcategoryName e DimProductCategory e desmarque a caixa de seleção Usar Nome da Coluna Original como Prefixo antes de selecionar OK.

    Ao selecionar essas duas colunas, uma transformação será aplicada para a junção à tabela DimProductSubcategory e a inclusão dessas colunas. A coluna DimProductCategory é, na verdade, outra tabela relacionada na fonte de dados.

    Os nomes das colunas de consulta devem ser sempre exclusivos. Se deixada marcada, essa caixa de seleção prefixaria cada coluna com o nome da coluna expandida (neste caso, DimProductSubcategory). Como é sabido que os nomes de coluna selecionados não colidem com nomes de coluna na consulta Produto, a opção é desmarcada.

  8. Observe que a transformação resultou na adição de duas colunas e que a coluna DimProductSubcategory foi removida.

  9. Expanda a coluna DimProductCategory e introduza apenas a coluna EnglishProductCategoryName.

  10. Renomeie as quatro seguintes colunas:

    • EnglishProductName como Product
    • StandardCost como Standard Cost (inclua um espaço)
    • EnglishProductSubcategoryName como Subcategory
    • EnglishProductCategoryName como Category

Na barra de status, confirme se a consulta tem seis colunas e 397 linhas.

Configurar a consulta Revendedor

Nesta tarefa, você vai configurar a consulta Revendedor.

  1. Selecione a consulta DimReseller e renomeie como Revendedor.

  2. Remova todas as colunas, exceto as seguintes:

    • ResellerKey
    • BusinessType
    • ResellerName
    • DimGeography

  3. Expanda a coluna DimGeography, para incluir apenas as três seguintes colunas:

    • City
    • StateProvinceName
    • EnglishCountryRegionName

  4. No cabeçalho da coluna Tipo de Negócio, selecione a seta para baixo e, em seguida, examine os valores de coluna distintos e observe os valores Warehouse e Ware House.

  5. Clique com o botão direito do mouse no cabeçalho da coluna Business Type e selecione Substituir Valores.

  6. Na janela Substituir Valores, configure os seguintes valores:

    • Na caixa Valor a ser Localizado, insira Ware House
    • Na caixa Substituir por, insira Warehouse

    Caixa de diálogo Substituir valores

  7. Renomeie as quatro seguintes colunas:

    • BusinessType como Business Type (inclua um espaço)
    • ResellerName como Reseller
    • StateProvinceName como State-Province
    • EnglishCountryRegionName como Country-Region

Na barra de status, confirme se a consulta tem seis colunas e 701 linhas.

Configurar a consulta Região

Nesta tarefa, você vai configurar a consulta Region.

  1. Selecione a consulta DimSalesTerritory e renomeie a consulta como Região.

  2. Aplique um filtro à coluna SalesTerritoryAlternateKey para remover o valor 0 (zero).

    Isso removerá uma linha.

  3. Remova todas as colunas, exceto as seguintes:

    • SalesTerritoryKey
    • SalesTerritoryRegion
    • SalesTerritoryCountry
    • SalesTerritoryGroup

  4. Renomeie as três seguintes colunas:

    • SalesTerritoryRegion como Region
    • SalesTerritoryCountry como Country
    • SalesTerritoryGroup como Group

Na barra de status, confirme se a consulta tem quatro colunas e dez linhas.

Configurar a consulta Vendas

Nesta tarefa, você vai configurar a consulta Vendas.

  1. Selecione a consulta FactResellerSales e renomeie-a como Vendas.

  2. Remova todas as colunas, exceto as seguintes:

    • SalesOrderNumber
    • OrderDate
    • ProductKey
    • ResellerKey
    • EmployeeKey
    • SalesTerritoryKey
    • OrderQuantity
    • UnitPrice
    • TotalProductCost
    • SalesAmount
    • DimProduct

    Observação: talvez você se lembre de que no laboratório Preparar dados no Power BI Desktop um pequeno percentual de linhas FactResellerSales estava com os valores TotalProductCost ausentes. A coluna DimProduct foi incluída para recuperar a coluna de custo padrão do produto, a fim de corrigir os valores ausentes.

  3. Expanda a coluna DimProduct, desmarque todas as colunas e inclua apenas a coluna StandardCost.

  4. Para criar uma coluna personalizada, na guia de faixa de opções Adicionar Coluna, no grupo Geral, selecione Coluna Personalizada.

    Imagem 5664

  5. Na janela Coluna Personalizada, na caixa Nome da Nova Coluna, substitua o texto por Cost.

  6. Na caixa Fórmula de coluna personalizada, insira a seguinte expressão (após o símbolo de igual) e salve a nova coluna:

    ` if [TotalProductCost] = null then [OrderQuantity] * [StandardCost] else [TotalProductCost] `

    Observação: você pode copiar a expressão do arquivo Snippets.txt na pasta 02-load-data.

    Essa expressõa testa se o valor TotalProductCost está ausente. Nesse caso, ele produz um valor multiplicando o valor de OrderQuantity pelo valor StandardCost;caso contrário, ele usa o valor de TotalProductCost existente.

  7. Remova as duas seguintes colunas:

    • TotalProductCost
    • StandardCost

  8. Renomeie as três seguintes colunas:

    • OrderQuantity como Quantity
    • UnitPrice como Unit Price (inclua um espaço)
    • SalesAmount como Sales

  9. Para modificar o tipo de dados da coluna, no cabeçalho da coluna Quantity, à esquerda do nome da coluna, selecione o ícone 1,2 e escolha Número Inteiro.

    A configuração correta do tipo de dados é importante. Quando a coluna contiver um valor numérico, também será importante escolher o tipo correto se você esperar fazer cálculos matemáticos.

    Imagem 5667

  10. Modifique os tipos de dados das três colunas a seguir para Número Decimal Fixo.

    O tipo de dados de número decimal fixo permite 19 dígitos e permite maior precisão para evitar erros de arredondamento. É importante usar o tipo de número decimal fixo para valores financeiros ou taxas (como taxas de câmbio).

    • Preço Unitário
    • Vendas
    • Custo

Na barra de status, confirme se a consulta tem dez colunas e mais de 999 linhas. Um máximo de 1.000 linhas será carregado como os dados de visualização para cada consulta.

Configurar a consulta Metas

Nesta tarefa, você vai configurar a consulta Targets.

  1. Selecione a consulta ResellerSalesTargets e renomeie como Metas.

    Observação: se você receber uma mensagem de aviso solicitando que você especifique como se conectar, selecione Editar credenciais e use o acesso anônimo.

  2. Para transformar as colunas de 12 meses (M01-M12) em linhas, primeiro, faça uma seleção múltipla dos cabeçalhos das colunas Ano e EmployeeID.

  3. Clique com o botão direito do mouse em um dos cabeçalhos da coluna selecionados e, no menu de contexto, selecione Transformar Outras Colunas em Linhas.

  4. Observe que os nomes das colunas agora são exibidos na coluna Attribute e os valores são exibidos na coluna Value.

  5. Aplique um filtro à coluna Value para remover os valores de hífen (-).

    Talvez você se lembre de que o caractere de hífen foi usado no arquivo CSV de origem para representar zero (0).

  6. Renomeie as duas seguintes colunas:

    • Atributo para MonthNumber (não há espaço)
    • Value como Target

  7. Para preparar os valores da coluna MonthNumber, clique com o botão direito do mouse no cabeçalho da coluna MonthNumber e selecione Substituir Valores.

    Agora você aplicará transformações para produzir uma coluna de data. A data será derivada das colunas Ano e MonthNumber. Você criará a coluna usando o recurso Colunas de Exemplos.

  8. Na janela Substituir Valores, na caixa Valor a Ser Localizado, insira M e deixe Substituir por vazio.

  9. Modifique o tipo de dados da coluna MonthNumber para Número Inteiro.

  10. Na guia de faixa de opções Adicionar Coluna, no grupo Geral, selecione o ícone de Coluna com Base em Exemplos.

    Imagem 5675

  11. Observe que a primeira linha se refere ao ano 2017 e ao mês número 7.

  12. Na coluna Coluna 1, na primeira célula da grade, insira 7/1/2017 e pressione Enter.

    Note: a máquina virtual usa as configurações regionais dos EUA e, portanto, essa data é, na verdade, 1º de julho de 2017. Outras configurações regionais podem pedir um 0 antes da data.

  13. Observe que as células da grade são atualizadas com os valores previstos.

    O recurso previu com precisão que você está combinando valores das colunas Ano e MonthNumber.

  14. Observe também a fórmula apresentada acima da grade da consulta.

    Imagem 5679

  15. Para renomear a nova coluna, clique duas vezes no cabeçalho da coluna Mesclado e renomeie a coluna como TargetMonth.

  16. Remova as seguintes colunas:

    • Ano
    • MonthNumber

  17. Modifique os seguintes tipos de dados de coluna:

    • Target como um número decimal fixo
    • TargetMonth como uma data

  18. Para multiplicar os valores de Target por 1.000, selecione o cabeçalho da coluna Target e, na guia de faixa de opções Transformar, no grupo Coluna de Número, selecione Padrão e Multiplicar.

    Talvez você se lembre que os valores de destino foram armazenados como milhares.

    Imagem 5682

  19. Na janela Multiplicar, na caixa Valor, insira 1000 e selecione OK.

Na barra de status, confirme se a consulta tem três colunas e 809 linhas.

Configurar a consulta ColorFormats

Nesta tarefa, você vai configurar a consulta ColorFormats.

  1. Selecione a consulta ColorFormats e observe que a primeira linha contém os nomes das colunas.

  2. Na guia de faixa de opções Página Inicial, no grupo Transformar, selecione Usar a Primeira Linha como Cabeçalho.

    Imagem 5688

Na barra de status, confirme se a consulta tem três colunas e dez linhas.

Atualizar a consulta do Produto

Nesta tarefa, você atualizará a consulta Product mesclando a consulta ColorFormats.

  1. Selecione a consulta Product.

  2. Para mesclar a consulta ColorFormats, na guia de faixa de opções Página Inicial, selecione a seta Combinar para baixo e, em seguida, selecione Mesclar Consultas.

    A mesclagem de consultas permite a integração de dados, neste caso, de fontes de dados diferentes (do SQL Server e de um arquivo CSV).

    Imagem 5654

  3. Na janela Mesclar, na grade da consulta Product, selecione o cabeçalho da coluna Color.

    Imagem 5655

  4. Abaixo da grade da consulta Product, na lista suspensa, selecione a consulta ColorFormats.

    Figura 21

  5. Na grade da consulta ColorFormats, selecione o cabeçalho da coluna Color.

  6. Quando a janela Níveis de Privacidade é aberta, para cada uma das duas fontes de dados, na lista suspensa correspondente, selecione Organizacional e Salvar.

    Os níveis de privacidade podem ser configurados para a fonte de dados para determinar se os dados podem ser compartilhados entre fontes. Definir cada fonte de dados como Organizacional permite que eles compartilhem dados, se necessário. As fontes de dados privadas nunca podem ser compartilhadas com outras fontes de dados. Isso não significa que os dados privados não possam ser compartilhados; mas sim que o mecanismo do Power Query não pode compartilhar dados entre as fontes.

    Imagem 5691

  7. Na janela Mesclar, use o Tipo de Ingresso padrão mantendo a seleção de Externa Esquerda e selecione OK.

  8. Expanda a coluna ColorFormats para incluir as duas seguintes colunas:

    • Background Color Format
    • Font Color Format

Na barra de status, confirme se a consulta agora tem oito colunas e 397 linhas.

Atualizar a consulta ColorFormats

Nesta tarefa, você atualizará a consulta ColorFormats para desabilitar a carga dela.

  1. Selecione a consulta ColorFormats.

  2. No painel Configurações de Consulta, selecione o link Todas as Propriedades.

    Imagem 322

  3. Na janela Propriedades da Consulta, desmarque a caixa de seleção Habilitar Carregamento para o Relatório.

    Desabilitar a carga significa que ela não será carregada como uma tabela para o modelo de dados. Isso é feito porque a consulta foi mesclada com a consulta de Produto, que está habilitada para carregar no modelo de dados.

    Imagem 323

Revisão do produto final

  1. No Editor do Power Query, confirme se você tem oito consultas, nomeadas corretamente da seguinte maneira:

    • Salesperson
    • SalespersonRegion
    • Product
    • Reseller
    • Region
    • Vendas
    • Destinos
    • ColorFormats (não será carregada no modelo de dados)

  2. Selecione Fechar & Aplicar para carregar os dados no modelo e feche a janela do Editor do Power Query.

    Imagem 326

  3. Agora você poderá ver a tela no Power BI Desktop, com os painéis Filtros, Visualizações e Dados à direita. No painel Dados , observe as sete tabelas carregadas no modelo de dados.

    Imagem 3

Laboratório concluído