Saiba como combinar várias origens de dados (Power Query)

Nesta tarefa, vai importar produtos do ficheiro Produtos e Orders.xlsx (transferidos e renomeados acima) para um livro do Excel, promover linhas para cabeçalhos de coluna, remover algumas colunas e carregar a consulta para uma folha de cálculo.

Passo 1: ligar a um livro do Excel

1. Crie um livro do Excel.

2. Selecione Dados > Obter dados > a partir de > de ficheiros a partir do livro.

3. Na caixa de diálogo Importar Dados, procure e localize o ficheiro Products.xlsx que transferiu e, em seguida, selecione Abrir.

4. No painel Navegador , faça duplo clique na tabela Produtos . É apresentado o power Editor do Power Query.

Passo 2: Examinar os Passos da Consulta

Por predefinição, Power Query adiciona automaticamente vários passos como uma conveniência para si. Examine cada passo em Passos Aplicados no painel Definições da Consulta para saber mais.

1. Clique com o botão direito do rato no passo Origem e selecione Editar Definições. Este passo foi criado quando importou o livro.

2. Clique com o botão direito do rato no passo Navegação e selecione Editar Definições. Este passo foi criado quando selecionou a tabela na caixa de diálogo Navegação .

3. Clique com o botão direito do rato no passo Tipo Alterado e selecione Editar Definições. Este passo foi criado 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.

Passo 3: remover outras colunas para apresentar apenas as colunas de interesse

Neste passo, o utilizador remove todas as colunas exceto IDDoProduto, NomeDoProduto, IDDaCategoria e QuantidadePorUnidade.

1. Em Pré-visualização de Dados, selecione as colunas ProductID, ProductName, CategoryID e QuantityPerUnit (utilize Ctrl+Click ou Shift+Click).

2. Selecione Remover Colunas > Remover Outras Colunas.

   

Passo 4: carregar a consulta de produtos

Neste passo, vai carregar a consulta Produtos para uma folha de cálculo do Excel.

• Selecione Base > Fechar & Carregar. A consulta é apresentada numa nova folha de cálculo do Excel.

Resumo: Power Query passos criados na Tarefa 1

À medida que executa atividades de consulta no Power Query, os passos de consulta são criados e listados no painel Definições da Consulta, na lista Passos Aplicados. Cada passo da consulta possui uma fórmula do Power Query correspondente, também conhecida como a linguagem "M". Para obter mais informações sobre Power Query fórmulas, consulte Criar fórmulas de Power Query no Excel.

Tarefas	Passo da consulta	Fórmula
Importar um livro do Excel	Origem	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Selecione a tabela Produtos	Navegar	= Origem{[Item="Produtos",Kind="Tabela"]}[Dados]
Power Query deteta automaticamente tipos de dados de coluna	Tipo Alterado	= Table.TransformColumnTypes(Products_Table,{{"ProductID", Int64.Type}, {"ProductName", escreva text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", escreva text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}})
Remover outras colunas para apresentar apenas as colunas de interesse	Outras Colunas Removidas	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

Nesta tarefa, irá importar dados para o seu livro do Excel a partir do feed OData da Northwind de exemplo em http://services.odata.org/Northwind/Northwind.svc,expandir a tabela de Order_Details, remover colunas, calcular um total de linhas, transformar uma DataDaEncomenda, agrupar linhas por ProductID e Ano, mudar o nome da consulta e desativar a transferência de consultas para o livro do Excel.

Passo 1: Ligar a um Feed OData

1. Selecione Dados > Obter > de Dados de Outras Origens > a Partir do Feed OData.

2. Na caixa de diálogo Feed de OData, introduza o URL para o feed OData da Northwind.

3. Selecione OK.

4. No painel Navegador , faça duplo clique na tabela Encomendas .

Passo 2: expandir uma tabela Detalhes_Encomenda

Neste passo, o utilizador expande a tabela Detalhes_Encomenda que está relacionada com a tabela Encomendas, para combinar as colunas IDDoProduto, PreçoUnitário e Quantidade de Detalhes_Encomenda na tabela Encomendas. A operação Expandir combina colunas a partir de uma tabela relacionada numa tabela de assunto. Quando a consulta é executada, as linhas da tabela relacionada (Order_Details) são combinadas em linhas com a tabela primária (Encomendas).

No Power Query, uma coluna que contém uma tabela relacionada tem o valor Registo ou Tabela na célula. Estas são denominadas colunas estruturadas. O registo indica um único registo relacionado e representa umarelação um-para-um com os dados atuais ou a tabela primária. Tabela indica uma tabela relacionada e representa uma relação um-para-muitos com a tabela atual ou primária. Uma coluna estruturada representa uma relação numa origem de dados que tem um modelo relacional. Por exemplo, uma coluna estruturada indica uma entidade com uma associação de chave externa num feed OData ou numa relação de chave externa numa base de dados SQL Server.

Após expandir a tabela Detalhes_Encomenda, são adicionadas três novas colunas e linhas adicionais à tabela Encomendas, uma para cada linha na tabela aninhada ou relacionada.

1. Em Pré-visualização de Dados, desloque-se horizontalmente para a coluna Order_Details .

2. Na coluna Order_Details , selecione o ícone expandir ().

3. No menu pendente Expandir:

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

   2. Selecione IDDoProduto, PreçoUnitário e Quantidade.

   3. Selecione OK.

      

      Nota: No Power Query, pode expandir as tabelas ligadas a partir de uma coluna e agregar as colunas da tabela ligada antes de expandir os dados na tabela de assunto. Para mais informações sobre como executar operações de agregação, consulte o artigo Agregar dados a partir de uma coluna.

Passo 3: remover outras colunas para apresentar apenas as colunas de interesse

Neste passo, o utilizador remove todas as colunas exceto as colunas DataDaEncomenda, IDDoProduto, PreçoUnitário e Quantidade. 

1. Em Pré-visualização de Dados, selecione as seguintes colunas: 

   1. Selecione a primeira coluna, OrderID.

   2. Shift+Clique na última coluna, Transitário.

   3. Mantenha a tecla Controlo premida e clique nas colunas DataDaEncomenda, Detalhes_Encomenda.IDDoProduto, Detalhes_Encomenda.PreçoUnitário e Detalhes_Encomenda.Quantidade.

2. Clique com o botão direito do rato num cabeçalho de coluna selecionado e selecione Remover Outras Colunas.

Passo 4: calcular o total da linha para cada linha Detalhes_Encomenda

Neste passo, o utilizador cria uma Coluna Personalizada para calcular o total da linha para cada linha Detalhes_Encomenda.

1. Em Pré-visualização de Dados, selecione o ícone de tabela () no canto superior esquerdo da pré-visualização.

2. Clique em Adicionar Coluna Personalizada.

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

4. Na caixa Nome da nova coluna , introduza Total de Linhas.

5. Selecione OK.

Passo 5: transformar uma coluna de ano DataDaEncomenda

Neste passo, o utilizador transforma a coluna DataDaEncomenda para compor o ano da data da encomenda.

1. Em Pré-visualização de Dados, clique com o botão direito do rato na coluna OrderDate e selecione Transformar > Ano.

2. Mude o nome da coluna DataDaEncomenda para Ano:

   1. Clique duas vezes na coluna DataDaEncomenda e introduza Ano ou

   2. Right-Click na coluna OrderDate , selecione Mudar o Nome e introduza Ano.

Passo 6: agrupar linhas por IDDoProduto e Ano

1. Em Pré-visualização de Dados, selecione Ano e Order_Details.ProductID.

2. Right-Click um dos cabeçalhos e selecione Agrupar Por.

3. Na caixa de diálogo Agrupar Por:

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

   2. No menu pendente Operação, selecione Soma.

   3. No menu pendente Coluna, selecione Total da Linha.

4. Selecione OK.

   

Passo 7: mudar o nome de uma consulta

Antes de importar os dados de vendas para o Excel, mude o nome da consulta:

• No painel Definições da Consulta , na caixa Nome, introduza Total de Vendas.

Resultados: Consulta final da Tarefa 2

Após executar cada passo, terá uma consulta Total de Vendas no feed OData da Northwind.

Resumo: Power Query passos criados na Tarefa 2 

À medida que executa atividades de consulta no Power Query, os passos de consulta são criados e listados no painel Definições da Consulta, na lista Passos Aplicados. Cada passo da consulta possui uma fórmula do Power Query correspondente, também conhecida como a linguagem "M". Para obter mais informações sobre Power Query fórmulas, veja Saiba mais sobre fórmulas de Power Query.

Tarefas	Passo da consulta	Fórmula
Ligar a um feed OData	Origem	= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])
Selecionar uma tabela	Navegação	= Origem{[Nome="Encomendas"]}[Dados]
Expandir a tabela Detalhes_Encomenda	Expandir Detalhes_Encomenda	= Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})
Remover outras colunas para apresentar apenas as colunas de interesse	ColunasRemovidas	= Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})
Calcular o total da linha para cada linha Detalhes_Encomenda	Personalizado Adicionado	= 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 com Nome Mudado	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Transformar a coluna DataDaEncomenda para compor o ano	Ano Extraído	= Table.TransformColumns(#"Linhas Agrupadas",{{"Ano", Data.Ano, Int64.Type}})
Alterar para  nomes mais significativos, OrderDate e Year	Colunas com Nome Mudado 1	Table.RenameColumns (ColunaTransformada,{{"DataDaEncomenda", "Ano"}})
Agrupar linhas por IDDoProduto e Ano	LinhasAgrupadas	= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

O Power Query permite-lhe combinar múltiplas consultas ao intercalar ou acrescentá-las. A operação Intercalar é efetuada em qualquer uma das consultas do Power Query com uma forma de tabela, independente da origem de dados da qual os dados são provenientes. Para mais informações sobre combinar origens de dados, consulte Combinar múltiplas consultas.

Nesta tarefa, vai combinar as consultas Produtos e Total de Vendascom uma consulta Intercalar e Expandir e, em seguida, carregar a consulta Total de Vendas por Produto para o Modelo de Dados do Excel.

Passo 1: intercalar IDDoProduto numa consulta Total de Vendas

1. No livro do Excel, navegue para a consulta Produtos no separador Folha de cálculo Produtos .

2. Selecione uma célula na consulta e, em seguida, selecione Consulta > Intercalar.

3. Na caixa de diálogo Intercalar , selecione Produtos como a tabela primária e selecione Total de Vendas como a consulta secundária ou relacionada a intercalar. As Vendas Totais tornar-se-ão numa nova coluna estruturada com um ícone de expansão.

4. Para corresponder Total de Vendas a Produtos pelo IDDoProduto, selecione a coluna IDDoProduto a partir da tabela Produtos e a coluna Detalhes_Encomenda.IDDoProduto 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 origens de dados.

   2. Selecione Guardar.

6. Selecione OK.

   Nota de Segurança: Os Níveis de Privacidade impedem um utilizador de inadvertidamente combinar dados a partir de múltiplas origens de dados, que podem ser privadas ou organizacionais. Dependendo da consulta, um utilizador pode inadvertidamente enviar dados a partir de uma origem de dados privada para outra origem de dados que pode ser mal-intencionada. O Power Query analisa cada origem de dados e classifica-a num nível de privacidade definido como: Público, Organizacional e Privado. Para obter mais informações sobre os Níveis de Privacidade, consulte Definir Níveis de Privacidade.

   

Result

A operação Intercalar 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 (Total de Vendas). Selecione o ícone Expandir para adicionar novas colunas à tabela primária a partir da tabela secundária ou relacionada.

Passo 2: Expandir uma coluna unida

Neste passo, vai expandir a coluna unida com o nome NewColumn para criar duas novas colunas na consulta Produtos : Ano e Total de Vendas.

1. Em Pré-visualização de Dados, selecione Expandir ícone () junto a NovaColuna.

2. Na lista pendente Expandir :

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

   2. Selecione Ano e Total de Vendas.

   3. Selecione OK.

3. Mude o nome destas duas colunas para Ano e Total de Vendas.

4. Para saber que produtos e em que anos os produtos obtiveram o maior volume de vendas, selecione Ordenação Descendente por Total de Vendas.

5. Selecione Mudar o Nome para dar o nome Total de Vendas por Produto à consulta.

Result

Passo 3: carregar uma consulta Total de Vendas por Produto num Modelo de Dados do Excel

Neste passo, vai carregar uma consulta para um Modelo de Dados do Excel para criar um relatório ligado ao resultado da consulta. Depois de carregar dados para o Modelo de Dados do Excel, pode utilizar o Power Pivot para aprofundar a sua análise de dados.

1. Selecione Base > Fechar & Carregar.

2. Na caixa de diálogo Importar Dados , certifique-se de que seleciona Adicionar estes dados ao Modelo de Dados. Para obter mais informações sobre como utilizar esta caixa de diálogo, selecione o ponto de interrogação (?).

Result

Tem uma consulta Total de Vendas por Produto que combina dados do ficheiro Products.xlsx e do feed OData da Northwind. Esta consulta é aplicada a um modelo do Power Pivot. Além disso, as alterações à consulta modificam e atualizam a tabela resultante no Modelo de Dados.

Resumo: Power Query passos criados na Tarefa 3

À medida que executa atividades de consulta Intercalar no Power Query, os passos de consulta são criados e listados no painel Definições da Consulta, na lista Passos Aplicados. Cada passo da consulta possui uma fórmula do Power Query correspondente, também conhecida como a linguagem "M". Para obter mais informações sobre Power Query fórmulas, veja Saiba mais sobre fórmulas de Power Query.

Tarefas	Passo da consulta	Fórmula
Intercalar o IDDoProduto numa consulta Total de Vendas	Origem (origem de dados para a operação Intercalar)	= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
Expandir uma coluna de intercalação	Total de Vendas Expandidas	= Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})
Mudar o nome de duas colunas	Colunas com Nome Mudado	= Table.RenameColumns(#"Total De Vendas Expandidas",{{"Total de Vendas.Ano", "Ano"}, {"Total de Vendas.Total de Vendas", "Total de Vendas"}})
Ordenar o total de Vendas por ordem ascendente	Linhas Ordenadas	= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})