Aprenda a combinar várias fontes de dados (Consulta do Power)

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

Para executar este tutorial, você precisa da guia de trabalho Produtos. Na caixa de diálogo Salvar como, nomeie o arquivo como Produtos e Pedidos.

Nesta tarefa, você importa produtos do arquivo Products and Orders.xlsx (baixado e renomeado acima) para uma pasta de trabalho do Excel, promove linhas para os headers de coluna, remove algumas colunas e carrega a consulta em uma planilha.

Etapa 1: conectar a uma pasta de trabalho do Excel

  1. Criar uma pasta de trabalho do Excel.

  2. Selecione Dados>Obter dados > do arquivo > da pasta de trabalho.

  3. Na caixa de diálogo Importar Dados, procure e localize o arquivo Products.xlsx que você baixou e selecione Abrir.

  4. No painel Navegador, clique duas vezes na tabela Produtos. O Editor de Consulta doPower é exibido.

Etapa 2: Examinar as Etapas de Consulta

Por padrão, a Consulta do Power adiciona automaticamente várias etapas como uma conveniência para você. Examine cada etapa em Etapas Aplicadas no painel Configurações de Consulta para saber mais.

  1. Clique com o botão direito do mouse na etapa Origem e selecione Editar Configurações. Esta etapa foi criada quando você importou a workbook.

  2. Clique com o botão direito do mouse na etapa navegação e selecione Editar Configurações. Esta etapa foi criada quando você selecionou a tabela na caixa de diálogo Navegação.

  3. Clique com o botão direito do mouse na etapa Tipo alterado e selecione Editar Configurações. Esta etapa foi criada pelo Power Query que inferiu os tipos de dados de cada coluna. Selecione a seta para baixo à direita da barra de fórmulas para ver a fórmula completa.

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. Em Visualizaçãode Dados, selecione as colunas ProductID,ProductName,CategoryIDe QuantityPerUnit (use Ctrl+Click ou Shift+Click).

  2. Selecione Remover Colunas > Remover Outras Colunas.

    Ocultar outras Colunas

Etapa 4: Carregar a consulta de produtos

Nesta etapa, você carrega a consulta Produtos em uma planilha do Excel.

  • Selecione Home > Fechar & Carregar. A consulta aparece em uma nova planilha do Excel.

Resumo: Etapas de Consulta do Power criadas na Tarefa 1

À medida que você executa atividades de consulta no Power Query, as etapas de consulta são criadas e listadas no painel Configurações de 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 fórmulas de Consulta do Power, consulte Create Power Query formulas in Excel.

Tarefa

Etapa de consulta

Fórmula

Importar uma planilha do Excel

Origem

= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)

Selecione a tabela Produtos

Navegar

= Source{[Item="Products",Kind="Table"]}[Data]

A Consulta do Power detecta automaticamente tipos de dados de coluna

Tipo alterado

= Table.TransformColumnTypes(Products_Table,{{"ProductID", Int64.Type}, {"ProductName", digite text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", digite text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}})

Remover outras colunas para exibir apenas as colunas de interesse

Removidas Outras Colunas

= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

Nesta tarefa, você importa dados para sua planilha do Excel do feed OData northwind de exemplo no http://services.odata.org/Northwind/Northwind.svc,expande a tabela Order_Details, remova colunas, calcule um total de linhas, transforme um OrderDate, linhas de grupo por ProductID e Year, renomeie a consulta e desabilite o download de consulta para a planilha do Excel.

Etapa 1: Conectar-se a um feed OData

  1. Selecione Dados >Obter dados>de outras fontes > do feed OData.

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

  3. Selecione OK.

  4. No painel Navegador, clique duas vezes na tabela Pedidos.

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 é executado, as linhas da tabela relacionada (Order_Details) são combinadas em linhas com a tabela primária (Pedidos).

Na Consulta do Power, uma coluna que contém uma tabela relacionada tem o valor Record ou Table na célula. Essas colunas são chamadas de colunas estruturadas. Record indica um único registro relacionado e representa uma relação um para um com os dados atuais ou a tabela primária. A tabela indica uma tabela relacionada e representa uma relação de um para muitos com a tabela atual ou primária. Uma coluna estruturada representa uma relação em uma fonte de dados que tem um modelo relacional. Por exemplo, uma coluna estruturada indica uma entidade com uma associação de chave estrangeira em um feed OData ou relação de chave estrangeira em um banco de dados SQL Server externo.

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. Em Visualização deDados, role horizontalmente para a coluna Order_Details dados.

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

  3. No menu suspenso Expandir:

    1. Selecione (Selecionar Todas as Colunas) para limpar todas as colunas.

    2. Selecione ProductID,UnitPricee Quantity.

    3. Selecione OK.

      Expandir o link da Tabela Order_Details

      Observação: Na Consulta do Power, você pode expandir tabelas vinculadas de uma coluna e agregar as 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

  1. Em Visualização deDados, selecione as seguintes colunas:

    1. Selecione a primeira coluna, OrderID.

    2. Shift+Clique na última coluna, Shipper.

    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 em um header de coluna selecionado e selecione Remover Outras 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.

  1. Em Visualização deDados, selecione o ícone de tabela ( Ícone de tabela ) no canto superior esquerdo da visualização.

  2. Clique em Adicionar Coluna Personalizada.

  3. Na caixa de diálogo Coluna Personalizada, na caixa Fórmula de coluna personalizada, digite [Order_Details.UnitPrice] * [Order_Details.Quantity].

  4. Na caixa Novo nome da coluna, digite Line Total.

  5. Selecione 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. Em Visualização deDados, clique com o botão direito do mouse na coluna OrderDate e selecione Transformar > Ano.

  2. Renomeie a coluna DataPedido para Ano:

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

    2. Right-Click na coluna OrderDate, selecione Renomeare insira Ano.

Etapa 6: agrupar linhas por ID do Produto e Ano

  1. Em Visualização deDados, selecione Anoe Order_Details.ProductID.

  2. Right-Click um dos headers e selecione Group By.

  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. Selecione 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, renomeie a consulta:

  • No painel Configurações de Consulta, na caixa Nome, digite Total de vendas.

Resultados: Consulta final para a Tarefa 2

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

Total de Vendas

Resumo: Etapas de Consulta do Power criadas na Tarefa 2 

À medida que você executa atividades de consulta no Power Query, as etapas de consulta são criadas e listadas no painel Configurações de 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 fórmulas de Consulta do Power, consulte Learn about Power Query formulas.

Tarefa

Etapa de consulta

Fórmula

Conectar a um feed de OData

Origem

= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])

Selecionar uma tabela

Navegação

= Source{[Name="Orders"]}[Data]

Expandir o link da tabela Order_Details

Expandir Order_Details

= Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})

Remover outras colunas para exibir apenas as colunas de interesse

RemovedColumns

= Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})

Calcular o total de linhas para cada linha de Order_Details

Adicionado Personalizado

= Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

= Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

Alterar para um nome mais significativo, Lne Total

Colunas renomeadas

= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})

Transformar a coluna OrderDate para renderizar o ano

Ano Extraído

= Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}})

Alterar para 

nomes mais significativos, OrderDate e Year

Colunas renomeadas 1

Table.RenameColumns

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

Agrupar linhas por ID do Produto e Ano

GroupedRows

= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

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 Produtos e Vendas Totais usando uma operação mesclar e expandir e carregar a consulta Total de Vendas por Produto no Modelo de Dados do Excel.

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

  1. Na pasta de trabalho do Excel, navegue até a consulta Produtos na guia Planilha Produtos.

  2. Selecione uma célula na consulta e selecione Consulta > Mesclar.

  3. Na caixa de diálogo Mesclar, selecione Produtos como a tabela primária e selecione Total de Vendas como a consulta secundária ou relacionada a ser mesclada. Total de Vendas se tornará uma nova coluna estruturada com um ícone de expansão.

  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. Selecione Salvar.

  6. Selecione 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 obter mais informações sobre Níveis de Privacidade, consulte Set Privacy Levels.

    Caixa de diálogo Mesclar

Resultado

A operação Merge cria uma consulta. O resultado da consulta contém todas as colunas da tabela primária (Produtos) e uma única coluna estruturada tabela para a tabela relacionada (Total de Vendas). Selecione o ícone Expandir para adicionar novas colunas à tabela primária da tabela secundária ou relacionada.

Mesclagem final

Etapa 2: Expandir uma coluna mesclada

Nesta etapa, você expanda a coluna mesclada com o nome NewColumn para criar duas novas colunas na consulta Produtos: Ano e Vendas Totais.

  1. Em Visualização deDados, selecione Expandir ícone ( Expandir ) ao lado de NewColumn.

  2. Na lista lista listada Expandir:

    1. Selecione (Selecionar Todas as Colunas) para limpar todas as colunas.

    2. Selecione Ano e Total de Vendas.

    3. Selecione OK.

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

  4. Para descobrir quais produtos e em quais anos os produtos têm o maior volume de vendas, selecione Classificar Decrescente por Total de Vendas.

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

Resultado

Expandir link da tabela

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

Nesta etapa, você carrega uma consulta em um Modelo de Dados do Excelpara criar um relatório conectado ao resultado da consulta. Depois de carregar dados no Modelo de Dados do Excel,você pode usar o Power Pivot para aumentar a análise de dados.

  1. Selecione Home > Fechar & Carregar.

  2. Na caixa de diálogo Importar Dados, selecione Adicionar esses dados ao Modelo de Dados. Para obter mais informações sobre como usar essa caixa de diálogo, selecione o ponto de interrogação (?).

Resultado

Você tem uma consulta Total de Vendas por Produto que combina dados do arquivo Products.xlsx e do feed northwind OData. Essa consulta é aplicada a um modelo do Power Pivot. Além disso, as alterações na consulta modificam e atualizem a tabela resultante no Modelo de Dados.

Resumo: Etapas de Consulta do Power criadas na Tarefa 3

À medida que você executa as atividades de consulta Merge na Consulta do Power, as etapas de consulta são criadas e listadas no painel Configurações de 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 fórmulas de Consulta do Power, consulte Learn about Power Query formulas.

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(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total de Vendas", JoinKind.LeftOuter)

Expandir uma coluna de mesclagem

Vendas totais expandidas

= Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})

Renomear duas colunas

Colunas renomeadas

= Table.RenameColumns(#"Vendas Totais Expandidas",{{"Total de Vendas.Ano", "Ano"}, {"Total de Vendas.Total de Vendas", "Total de Vendas"}})

Classificar vendas totais em ordem crescente

Linhas ordenadas

= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})

Confira também

Power Query for Excel Help

Precisa de mais ajuda?

Expanda suas habilidades no Office
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.

×