Combinar dados de várias fontes de dados (Power Query)

Importante :  Este artigo foi traduzido por um sistema de tradução automática, leia o aviso de isenção de responsabilidade. Para sua referência, veja a versão em inglês deste artigo aqui.

Observação :  O Power Query é conhecido como o recurso Obter e Transformar no Excel 2016. As informações fornecidas aqui se aplicam a ambos. Para saber mais, confira o artigo Obter e Transformar no Excel 2016.

Neste tutorial, você usará o Editor de consulta do Power Query para importar dados de um arquivo local do Excel que contém informações de produto e de um OData feed que contém informações de ordem de produto. Execute as etapas de transformação e agregação e combinar dados de ambas as fontes para produzir um relatório de Total de vendas por produto e ano.

Para realizar este tutorial, você precisará da pasta de trabalho Produtos e Pedidos. Na caixa de diálogo Salvar como, nomeie o arquivo como Produtos e Pedidos.

Neste tutorial

Tarefa 1: importar produtos para uma pasta de trabalho do Excel

Etapa 1: conectar a uma pasta de trabalho do Excel

Etapa 2: promover a primeira linha a cabeçalhos de coluna da tabela

Etapa 3: remover outras colunas para exibir apenas as colunas de interesse

Etapas criadas do Power Query

Etapa 4: importar uma consulta de produtos

Tarefa 2: importar dados de ordem de um feed de OData

Etapa 1: conectar a um feed OData

Etapa 2: expandir uma tabela Order_Details

Expandir o link da Tabela Order_Details

Etapa 3: remover outras colunas para exibir apenas as colunas de interesse

Remover as colunas selecionadas

Etapa 4: calcular o total de linhas para cada linha de Order_Details

Calcular o total de linhas para cada linha de Order_Details

Etapa 5: transformar uma coluna de ano OrderDate

Etapa 6: agrupar linhas por ID do Produto e Ano

Etapa 7: renomear uma consulta

Resultados finais da consulta

Etapas criadas do Power Query

Etapa 8: desabilitar o download de consulta em uma pasta de trabalho do Excel

Desabilitar o download de uma consulta

Tarefa 3: combinar as consultas de Produtos e Total de Vendas

Etapa 1: mesclar ProductID em uma consulta de Total de Vendas

Etapa 2: expandir uma coluna de mesclagem

Expandir o link da tabela NewColumn

Etapas criadas do Power Query

Etapa 3: carregar uma consulta de Total de Vendas por Produto em um Modelo de Dados do Excel

Carregar a consulta de Total de Vendas por Produto em um Modelo de Dados do Excel

Consulta de Total de Vendas Final por Produto

Tarefa 1: importar produtos para uma pasta de trabalho do Excel

Nesta tarefa, você importa produtos do arquivo Produtos e Pedidos.xlsx em uma pasta de trabalho do Excel.

Etapa 1: conectar a uma pasta de trabalho do Excel

  1. Criar uma pasta de trabalho do Excel.

  2. Na guia de faixa de opções POWER QUERY, clique em Do arquivo > Do Excel.

  3. Na caixa de diálogo de pesquisa do Excel, procure ou digite o caminho de Produtos e Orders.xlsx para importar ou vincular a um arquivo.

  4. No painel do Navegador, clique duas vezes na planilha Produtos ou clique em Produtos e clique em Editar Consulta. Ao editar uma consulta, ou conectar-se a uma nova fonte de dados, a janela do Editor de Consultas é exibida.

    Observação : Para ter acesso a um vídeo rápido sobre como exibir o Editor de Consultas, consulte o final deste artigo.

Etapa 2: promover a primeira linha a cabeçalhos de coluna da tabela

Na grade Visualização de Consulta, a primeira linha da tabela não contém os nomes de coluna da tabela. Para promover a primeira linha a cabeçalho de coluna da tabela:

  1. Clique no ícone de tabela ( Ícone de tabela ) no canto superior esquerdo da visualização de dados.

  2. Clique em Usar Primeira Linha como Cabeçalhos.

Promova a primeira linha para cabeçalhos de coluna da tabela

Etapa 3: remover outras colunas para exibir apenas as colunas de interesse

Nesta etapa, você remove todas as colunas exceto ProductID, ProductName, CategoryID e QuantityPerUnit.

  1. Na grade de Visualização de Consulta, selecione as colunas ProductID, ProductName, CategoryID e QuantityPerUnit (usando Ctrl + clique ou Shift + clique).

  2. Na faixa de opções do Editor de Consulta, clique em Remover Colunas > Remover Outras Colunas ou clique com o botão direito do mouse em um cabeçalho de coluna e clique em Remover Outras Colunas.

    Ocultar outras Colunas

Etapas criadas do Power Query

Enquanto você executa as atividades de consulta no Power Query, as etapas de consulta são criadas e listadas no painel Configurações da Consulta, na lista de ETAPAS APLICADAS. Cada etapa de consulta tem uma fórmula correspondente de Power Query, também conhecida como a linguagem "M". Para obter mais informações sobre a Linguagem de Fórmulas do Power Query, consulte Saiba mais sobre as fórmulas do Power Query.

Tarefa

Etapa de consulta

Fórmula

Conectar a uma pasta de trabalho do Excel

Origem

Fonte {[nome = "Produtos"]} [dados]

Promover a primeira linha a cabeçalho de coluna da tabela

FirstRowAsHeader

Table.PromoteHeaders

(Produtos)

Remover outras colunas para exibir apenas as colunas de interesse

RemovedOtherColumns

Table.SelectColumns

(FirstRowAsHeader, {"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

Etapa 4: importar uma consulta de produtos

Nesta etapa, você importa a consulta Produtos em sua pasta de trabalho do Excel.

  1. Na faixa de opções Editor de Consulta, clique em Aplicar e Fechar. Os resultados são exibidos em uma nova planilha do Excel.

Início da Página

Tarefa 2: importar dados de ordem de um feed de OData

Nesta tarefa, você importa dados para sua pasta de trabalho do Excel a partir do exemplo de feed OData da Northwind em http://services.odata.org/Northwind/Northwind.svc.

Etapa 1: conectar a um feed OData

  1. Na guia da faixa de opções do POWER QUERY, clique em De Outras Fontes > Do Feed OData.

  2. Na caixa de diálogo Feed OData, digite a URL do feed OData da Northwind.

  3. Clique em OK.

  4. No painel do Navegador, clique duas vezes na tabela Pedidos ou clique em Pedidos e clique em Editar.

Observação :  Ao passar o mouse sobre uma tabela, você verá uma visualização prévia da tabela.

Passe o mouse sobre uma Fonte de Dados

Etapa 2: expandir uma tabela Order_Details

Nesta etapa, você expande a tabela Order_Details relacionada à tabela Pedidos para combinar as colunas ID do Produto, PreçoUnitário e Quantidade de Order_Details na tabela Pedidos. A operação Expandir combina colunas de uma tabela relacionada em uma tabela de assunto. Quando a consulta é executada, as linhas da tabela relacionada (Order_Details) são combinadas em linhas da tabela de assunto (Pedidos).

No Power Query, uma coluna contendo um link para uma tabela relacionada tem um link de Entrada ou de Tabela. Um link de Entrada navega até um único registro relacionado e representa uma relação um para um com uma tabela de assunto. Um link de Tabela navega até uma tabela relacionada e representa uma relação de um para muitos com uma tabela de assunto. Um link representa propriedades de navegação em uma fonte de dados dentro de um modelo relacional. Para um feed de OData, as propriedades de navegação representam uma entidade com uma associação de chave estrangeira. Em um banco de dados, como o SQL Server, as propriedades de navegação representam relações de chave estrangeira no banco de dados.

Expandir o link da tabela Order_Details

Depois de expandir a tabela Order_Details, três novas colunas e linhas adicionais são acrescentadas à tabela Pedidos, um para cada linha na tabela aninhada ou relacionada.

  1. Percorra o painel Visualização de Consulta até encontrar a coluna Order_Details.

  2. Na coluna Order_Details, clique no ícone de expansão ( Expandir ).

  3. No menu suspenso Expandir:

    1. Clique em (Selecionar todas as colunas) para limpar todas as colunas.

    2. Clique em ID do Produto, Preço Unitário e Quantidade.

    3. Clique em OK.

      Expandir o link da Tabela Order_Details

      Observação :  No Power Query, você pode expandir tabelas vinculadas de e para uma coluna, e também executar operações de agregação nas colunas da tabela vinculada antes de expandir os dados na tabela de assunto. Para saber mais sobre como executar operações de agregação, consulte Agregar dados de uma coluna.

Etapa 3: remover outras colunas para exibir apenas as colunas de interesse

Nesta etapa, você remove todas as colunas exceto DataPedido, ID do Produto, PreçoUnitário e Quantidade. Na tarefa anterior, você usou Remover Outras Colunas. Nesta tarefa, você remove colunas selecionadas.

Remover as colunas selecionadas

  1. No painel de Visualização de Consulta, selecione todas as colunas:

    1. Clique na primeira coluna (ID do Pedido).

    2. Pressione Shift + clique na última coluna (Transportador).

    3. Pressione CTRL + clique nas colunas DataPedidoOrder_Details.ID do Produto, Order_Details.PreçoUnitário e Order_Details.Quantidade

  2. Clique com o botão direito do mouse sobre o cabeçalho de coluna selecionada e clique em Remover Colunas.

Etapa 4: calcular o total de linhas para cada linha de Order_Details

Nesta etapa, você cria uma Coluna Personalizada para calcular o total de linhas de cada linha Order_Details.

Calcular o total de linhas para cada linha de Order_Details

  1. No painel de Visualização de Consulta, clique no ícone de tabela ( Ícone de tabela ) no canto superior esquerdo da visualização.

  2. Clique em Inserir Coluna > Personalizada.

  3. Na caixa de diálogo Inserir Coluna Personalizada, na caixa de texto Fórmula de Coluna Personalizada, digite [Order_Details.PreçoUnitário] * [Order_Details.Quantidade].

  4. Na caixa de texto Novo nome da coluna, digite Total da Linha.

  5. Clique em OK.

Calcular o total de linhas para cada linha de Order_Details

Etapa 5: transformar uma coluna de ano OrderDate

Nesta etapa, você transforma a coluna DataPedido para renderizar o ano da data do pedido.

  1. Na grade de Visualização, clique com botão direito do mouse na coluna DataPedido e clique em Transformar > Ano.

  2. Renomeie a coluna DataPedido para Ano:

    1. Clique duas vezes na coluna DataPedido e digite Ano ou

    2. Clique com o botão direito do mouse na coluna DataPedido, clique em Renomear e digite Ano.

Etapa 6: agrupar linhas por ID do Produto e Ano

  1. Na grade Visualização de Consulta, selecione Ano e Order_Details.ID do Produto.

  2. Clique com o botão direito do mouse em um dos cabeçalhos e clique em Agrupar por.

  3. Na caixa de diálogo Agrupar por:

    1. Na caixa de texto Novo nome da coluna, digite Total de Vendas.

    2. Na caixa suspensa Operação, selecione Soma.

    3. Na caixa suspensa Coluna, selecione Total da Linha.

  4. Clique em OK.

    Caixa de diálogo Agrupar por para Operações de Agregação

Etapa 7: renomear uma consulta

Antes de importar os dados de vendas para o Excel, nomeie a consulta Total de Vendas:

  1. No painel Configurações da Consulta, na caixa de texto Nome, digite Total de Vendas.

Resultados finais da consulta

Depois de executar cada etapa, você terá uma consulta de totais de vendas sobre o feed de OData da Northwind.

Total de Vendas

Etapas criadas do Power Query

Enquanto você executa as atividades de consulta no Power Query, as etapas de consulta são criadas e listadas no painel Configurações da Consulta, na lista de ETAPAS APLICADAS. Cada etapa de consulta tem uma fórmula correspondente de Power Query, também conhecida como a linguagem "M". Para obter mais informações sobre a Linguagem de Fórmulas do Power Query, consulte Saiba mais sobre as fórmulas do Power Query.

Tarefa

Etapa de consulta

Fórmula

Conectar a um feed de OData

Origem

Fonte {[nome = "Pedidos"]} [dados]

Expandir o link da tabela Order_Details

Expandir Order_Details

Table.ExpandTableColumn

(Pedidos, "Order_Details", {"ProductID", "UnitPrice", "Quantidade"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})

Remover outras colunas para exibir apenas as colunas de interesse

RemovedColumns

Table.RemoveColumns

(#"Expandir Order_Details", {"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Frete", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Cliente", "Empregado", "Remetente"})

Calcular o total de linhas para cada linha Order_Details

InsertedColumns

Table.AddColumn

(RemovedColumns, "Personalizada", cada [Order_Details.UnitPrice] * [Order_Details.Quantity])

Transformar a coluna OrderDate para renderizar o ano

RenamedColumns

Table.RenameColumns

(InsertedCustom, {{"Personalizado", "Linha Total"}})

TransformedColumn

Table.TransformColumns

(RenamedColumns, {{"OrderDate", Date.Year}})

RenamedColumns1

Table.RenameColumns

(TransformedColumn, {{"OrderDate", "Ano"}})

Agrupar linhas por ID do Produto e Ano

GroupedRows

Table.Group
(RenamedColumns1, {"Ano", "Order_Details.ProductID"}, {{"Total de Vendas", cada List. Sum ([Linha Total]), digite número}})

Etapa 8: desabilitar o download de consulta em uma pasta de trabalho do Excel

Uma vez que a consulta de Total de Vendas não representa o relatório final de Total de Vendas por Produto e Ano, o download de consulta é desabilitado na pasta de trabalho do Excel. Quando a opção Carregar para Planilha estiver Desativada no painel Configurações da Consulta, o resultado de dados desta consulta não é baixado, mas a consulta ainda pode ser combinada com outras consultas para se alcançar o resultado desejado. Você vai aprender a combinar essa consulta com a consulta de Produtos na próxima tarefa.

Desabilitar um download de consulta

  1. No painel Configurações da Consulta, desmarque Carregar para planilha.

  2. Na faixa de opções Editor de Consulta, clique em Aplicar e Fechar. No painelConsultas de Pasta de Trabalho, a consulta Total de Vendas exibe A carga está desabilitada.

    Desabilitar o download de uma consulta

Início da Página

Tarefa 3: combinar as consultas de Produtos e Total de Vendas

O Power Query permite que você combine várias consultas, mesclando ou anexando-as. A operação Mesclar é executada em qualquer consulta do Power Query com um formato tabular, independente da fonte de dados que os dados provenham. Para saber mais sobre como combinar fontes de dados, consulte Combinar várias consultas.

Nesta tarefa, você combina as consultas de Produtos e Total de Vendas, usando uma etapa de consulta Mesclar e Expandir.

Etapa 1: mesclar ProductID em uma consulta de Total de Vendas

  1. Na pasta de trabalho do Excel, navegue até a consulta de Produtos em Planilha2.

  2. Na guia da faixa de opções QUERY, clique em Mesclar.

  3. Na caixa de diálogo Mesclar, selecione Produtos como tabela primária e Total de vendas como segunda consulta ou relacionada para mesclar. Total de vendas se tornará uma nova coluna expansível.

  4. Para coincidir o Total de vendas com Produtos através do ProductID, selecione a coluna ProductID da tabela Produtos e a coluna Order_Details.ProductID da tabela Total de vendas.

  5. Na caixa de diálogo Níveis de Privacidade:

    1. Selecione Organizacional para o seu nível de isolamento de privacidade para ambas as fontes de dados.

    2. Clique em Salvar.

  6. Clique em OK.

    Observação de segurança :  Os Níveis de Privacidade impedem que um usuário combine inadvertidamente dados de várias fontes de dados que podem ser privadas ou organizacionais. Dependendo da consulta, um usuário poderia inadvertidamente enviar dados da fonte de dados privada para outra fonte de dados que pode ser mal-intencionada. O Power Query analisa cada fonte de dados e a classifica em um nível definido de privacidade: Pública, organizacional e privada. Para saber mais sobre níveis de privacidade, consulte Níveis de privacidade.

    Caixa de diálogo Mesclar

Depois que você clicar em OK, a operação Mesclar cria uma consulta. O resultado da consulta contém todas as colunas da tabela primária (Produtos) e uma única coluna contendo um link de navegação para a tabela relacionada (Total de Vendas). Uma operação Expandir adiciona novas colunas na tabela primária ou de assunto da tabela relacionada.

Mesclagem final

Etapa 2: expandir uma coluna de mesclagem

Nesta etapa, você expande a coluna de mesclagem com o nome NovaColuna para criar duas novas colunas na consulta Produtos: Ano e Total de Vendas.

Expandir o link da tabela NewColumn

  1. Na grade de Visualização da Consulta, clique em Nova Coluna no ícone de expansão ( Expandir ).

  2. No menu suspenso Expandir:

    1. Clique em (Selecionar todas as colunas) para limpar todas as colunas.

    2. Clique em Ano e Total de Vendas.

    3. Clique em OK.

  3. Renomear essas duas colunas para Ano e Total de Vendas.

  4. Classificar em Ordem Decrescente por Total de Vendas para descobrir em quais anos e quais produtos tiveram o maior volume de vendas.

  5. Renomear a consulta para Total de Vendas por Produto.

Expandir link da tabela

Etapas criadas do Power Query

Enquanto você executa as atividades de consulta de Mesclagem no Power Query, as etapas de consulta são criadas e listadas no painel Configurações da Consulta, na lista ETAPAS APLICADAS. Cada etapa da consulta tem uma fórmula correspondente do Power Query, também conhecida como a linguagem "M". Para obter mais informações sobre a Linguagem de Fórmulas do Power Query, consulte Saiba mais sobre as fórmulas do Power Query.

Tarefa

Etapa de consulta

Fórmula

Mesclar ProductID em uma consulta de totais de vendas

Fonte (fonte de dados para a operação Mesclar)

Table.NestedJoin

(Produtos, {"ProductID"}, #"Total Sales",{"Order_Details.ProductID"},"NewColumn")

Expandir uma coluna de mesclagem

ExpandirNovaColuna

Table.ExpandTableColumn

(Fonte, "NewColumn", {"Ano", "Vendas totais"}, {"NewColumn.Year", "Vendas de NewColumn.Total"})

RenamedColumns

Table.RenameColumns

(#"Expandir NewColumn",{{"NewColumn.Year", "Ano"}, {"NewColumn.Total vendas", "vendas totais"}})

LinhasClassificadas

Table.Sort

(RenamedColumns, {{"vendas totais", Order.Descending}})

Etapa 3: carregar uma consulta de Total de Vendas por Produto em um Modelo de Dados do Excel

Nesta etapa, você desativa a opção Carregar para Planilha e carrega uma consulta no Modelo de Dados do Excel para criar um relatório conectado ao resultado da consulta. Além de carregar os resultados da consulta em uma planilha do Excel, o Power Query permite que você carregue um resultado de consulta em um Modelo de Dados do Excel. Depois de carregar dados para o Modelo de Dados do Excel, você pode usar Power Pivot e Power View para nova análise de dados.

Carregar a consulta de Total de Vendas por Produto em um Modelo de Dados do Excel

  1. No painel Configurações da Consulta, desmarque Carregar na planilha e marque Carregar para modelo de dados.

  2. Para carregar a consulta para o Modelo de Dados do Excel, clique Aplicar e Fechar.

Carregar Modelo de Dados do Excel

Consulta de Total de Vendas Final por Produto

Depois de executar cada etapa, você terá uma consulta Total de Vendas por Produto que combina dados do arquivo Products and Orders.xlsx e o feed OData da Northwind. Esta consulta pode ser aplicada a um Power Pivot modelo. Além disso, as alterações à consulta no Power Query modificam e atualizam a tabela resultante no Power Pivot modelo.

Início da Página

Observação : O Editor de Consultas só aparece quando você carrega, edita ou cria uma nova consulta usando o Power Query. Mostra o vídeo a seguir o Editor de consulta janela que aparecem após editar uma consulta a partir de uma pasta de trabalho do Excel. Para exibir o Editor de Consultas sem carregar ou editar uma consulta existente de pasta de trabalho, na seção Obter Dados Externos da guia da faixa de opções do Power Query, selecione De Outras Fontes > Consulta em Branco. O vídeo a seguir mostra uma maneira de exibir o Editor de Consultas.

Como ver o Editor de Consulta no Excel

Observação : Aviso de Isenção de Tradução Automática: Este artigo foi traduzido por computador, sem intervenção humana. A Microsoft oferece essas traduções automáticas para ajudar as pessoas que não falam inglês a aproveitar os textos escritos sobre produtos, serviços e tecnologias da Microsoft. Como este artigo foi traduzido automaticamente, é possível que contenha erros de vocabulário, sintaxe ou gramática.

Expanda suas habilidades
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

Obrigado por seus comentários!

Agradecemos pelos seus comentários! Parece que pode ser útil conectar você a um de nossos agentes de suporte do Office.

×