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

Nesta tarefa, você importa produtos do arquivo Produtos e Orders.xlsx (baixados e renomeados acima) em uma pasta de trabalho do Excel, promove linhas para cabeçalhos 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 > de dados do > de 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 Power Editor de Consultas é exibido.

Etapa 2: examinar as etapas de consulta

Por padrão, Power Query 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. Essa etapa foi criada quando você importou a pasta de trabalho.

2. Clique com o botão direito do mouse na etapa Navegação e selecione Editar Configurações. Essa 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. Essa etapa foi criada por Power Query que inferiram 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ção de Dados, selecione as colunas ProductID, ProductName, CategoryID e QuantityPerUnit (use Ctrl+Click ou Shift+Click).

2. Selecione Remover colunas > Remover outras colunas.

   

Etapa 4: carregar a consulta de produtos

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

• Selecione Home > Fechar & Carga. A consulta é exibida em uma nova planilha do Excel.

Resumo: Power Query etapas 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 Power Query, consulte Criar fórmulas de Power Query no Excel.

Tarefa	Etapa de consulta	Fórmula
Importar uma pasta de trabalho 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]
Power Query detecta automaticamente tipos de dados de coluna	Tipo Alterado	= Table.TransformColumnTypes(Products_Table,{{"ProductID", Int64.Type}, {"ProductName", type text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Descontinuado", tipo logic}})
Remover outras colunas para exibir apenas as colunas de interesse	Outras Colunas Removidas	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

Nesta tarefa, você importa dados para sua pasta de trabalho do Excel do feed northwind OData 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 da consulta na pasta de trabalho do Excel.

Etapa 1: Conectar-se a um Feed do OData

1. Selecione Dados > Obter > de dados de outras fontes > do Feed do 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 é executada, as linhas da tabela relacionada (Order_Details) são combinadas em linhas com a tabela primária (Pedidos).

Em Power Query, uma coluna que contém uma tabela relacionada tem o valor Registro ou Tabela na célula. Elas são chamadas de colunas estruturadas. O registro indica um único registro relacionado e representa uma relação umpara 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 uma relação de chave estrangeira em um banco de dados SQL Server.

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 de Dados, role horizontalmente até a coluna Order_Details .

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

3. No menu suspenso Expandir:

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

   2. Selecione ProductID, UnitPrice e Quantity.

   3. Selecione OK.

      

      Observação: Em Power Query, você pode expandir tabelas vinculadas a partir 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 de Dados, 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 cabeçalho 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 de Dados, selecione o í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 de fórmulas de coluna personalizada , insira [Order_Details.UnitPrice] * [Order_Details.Quantity].

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

5. Selecione OK.

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 de Dados, 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 Renomear e insira Ano.

Etapa 6: agrupar linhas por ID do Produto e Ano

1. Em Visualização de Dados, selecione Ano e Order_Details.ProductID.

2. Right-Click um dos cabeçalhos e selecione Grupo 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. Selecione OK.

   

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 NomeinsiraVendas Totais.

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.

Resumo: Power Query etapas 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 Power Query, consulte Saiba mais sobre Power Query fórmulas.

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	Personalização Adicionada	= 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 renomeada 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 Mesclagem e Expansão e carrega a consulta Vendas Totais 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 Consultar> Mesclagem.

3. Na caixa de diálogo Mesclagem , selecione Produtos como a tabela primária e selecione Vendas Totais como a consulta secundária ou relacionada a mesclagem. O 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 Definir níveis de privacidade.

   

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 de Tabela para a tabela relacionada (Vendas Totais). Selecione o ícone Expandir para adicionar novas colunas à tabela primária na tabela secundária ou relacionada.

Etapa 2: Expandir uma coluna mesclada

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

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

2. Na lista suspensa Expandir :

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

   2. Selecione Ano e Vendas Totais.

   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 receberam o maior volume de vendas, selecione Classificar Decrescente por Vendas Totais.

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

Resultado

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 Excel para 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 promover sua 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 do Northwind OData. Essa consulta é aplicada a um modelo do Power Pivot. Além disso, as alterações na consulta modificam e atualizam a tabela resultante no Modelo de Dados.

Resumo: Power Query etapas criadas na Tarefa 3

Ao executar atividades de consulta de mesclagem em 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 Power Query, consulte Saiba mais sobre Power Query fórmulas.

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 Sales", 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(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total sales"}})
Classificar vendas totais em ordem crescente	Linhas Classificadas	= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})