Neste tutorial, use o Editor de Consultas do Power Query para importar dados de um arquivo Excel local que contenha informações sobre o produto e de um feed OData que contenha informações sobre pedidos de produtos. Execute etapas de transformação e agregação e combine dados de ambas as fontes para criar um relatório de Total de Vendas por Produto e Ano .
Para concluir este tutorial, você precisa da pasta de trabalho Produtos . Na caixa de diálogo Salvar como, nomeie o arquivo como Produtos e Pedidos.
Tarefa 1: importar produtos para uma pasta de trabalho do Excel
Nesta tarefa, você importa produtos do arquivo Produtos e Orders.xlsx (baixado e renomeado na seção anterior) em uma pasta de trabalho do Excel. Em seguida, você promove linhas a cabeçalhos de coluna, remove algumas colunas e carrega a consulta em uma planilha.
Etapa 1: Conectar-se a uma pasta de trabalho do Excel
- Criar uma pasta de trabalho do Excel.
- Selecione Dados>Obter dados>do arquivo>da pasta de trabalho.
- Na caixa de diálogo Importar Dados , procure e localize o arquivo Products.xlsx que você baixou e selecione Abrir.
- No painel Navegador , clique duas vezes na tabela Produtos . O Editor do Power Query é exibido.
Etapa 2: examinar as etapas da consulta
Por padrão, 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.
- Clique com o botão direito do mouse na etapa Origem e selecione Editar configurações. Esta etapa foi criada quando você importou a pasta de trabalho.
- 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 .
- 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 colunas de interesse
Nesta etapa, você remove todas as colunas, exceto ProductID,ProductName, CategoryID e QuantityPerUnit.
- Na Visualização de Dados, selecione as colunas ProductID,ProductName, CategoryID e QuantityPerUnit (use Ctrl+Clique ou Shift+Click).
- 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 Página Inicial>,Fechar & Carregar. A consulta aparece em uma nova planilha do Excel.
Resumo: Etapas do Power Query criadas na Tarefa 1
À medida que você executa atividades de consulta no Power Query, ele cria etapas de consulta e as lista 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 fórmulas do Power Query, consulte a documentação do Power Query.
| 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] |
| O Power Query detecta automaticamente os 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}, {"Discontinued", type logical}}) |
| Remover outras colunas para exibir apenas as colunas de interesse | Outras Colunas Removidas | = Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"}) |
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 feed de OData Northwind de exemplo em http://services.odata.org/Northwind/Northwind.svc, expande a tabela Order_Details, remove colunas, calcula um total de linhas, transforma um OrderDate, agrupa linhas por IDProduto e Ano, renomeia a consulta e desabilita o download da consulta para a pasta de trabalho do Excel.
Etapa 1: conectar-se a um feed OData
- Selecionar Dados>Obter dados>de outras fontes>do feed OData.
- Na caixa de diálogo Feed OData, digite a URL do feed OData da Northwind.
- Selecione OK.
- 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).
No Power Query, uma coluna que contém uma tabela relacionada tem o valor Registro ou Tabela na célula. Eles são chamados de colunas estruturadas. Registro indica um único registro relacionado e representa uma relação um-para-um com os dados atuais ou 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 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 relacionamento de chave estrangeira em um banco de dados do SQL Server.
Depois de expandir a tabela Order_Details , três novas colunas e linhas adicionais serão adicionadas à tabela Pedidos , uma para cada linha na tabela aninhada ou relacionada.
Na Visualização de Dados, role horizontalmente até a coluna Order_Details .
Na coluna Order_Details , selecione o ícone expandir (
).No menu suspenso Expandir:
Selecione (Selecionar Todas as Colunas) para limpar todas as colunas.
Selecione ProductID,UnitPrice e Quantity.
Selecione OK.
Observação
No Power Query, 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 obter mais informações sobre como executar operações agregadas, consulte Agregar dados de uma coluna (Power Query).
Etapa 3: remover outras colunas para exibir apenas colunas de interesse
Nesta etapa, você remove todas as colunas, exceto as colunas DataDoPedido, IDProduto, PreçoUnitário e Quantidade .
Na Visualização de Dados, selecione as seguintes colunas:
- Selecione a primeira coluna, IDPedido.
- Shift+Clique na última coluna, Transportador.
- Pressione CTRL + clique nas colunas DataPedidoOrder_Details.ID do Produto, Order_Details.PreçoUnitário e Order_Details.Quantidade
Clique com o botão direito do mouse no cabeçalho de uma coluna selecionada 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.
- Na Visualização de Dados, selecione o ícone de tabela (
) no canto superior esquerdo da visualização. - Selecione Adicionar Coluna Personalizada.
- Na caixa de diálogo Coluna Personalizada, na caixa Fórmula de coluna personalizada, insira [Order_Details.PreçoUnitário] * [Order_Details.Quantidade].
- Na caixa Nome da nova coluna , insira Total da Linha.
- Selecione OK.
Passo 5: transformar uma coluna de ano DataDaEncomenda
Nesta etapa, você transforma a coluna DataPedido para renderizar o ano da data do pedido.
Em Pré-visualização de Dados, clique com o botão direito do rato na coluna DataDaEncomenda e selecione Transformar Ano>.
Renomeie a coluna DataPedido para Ano:
- Faça duplo clique na coluna DataDaEncomenda e introduza Ano ou
- Clique com o botão direito do rato na coluna DataDaEncomenda , selecione Mudar o Nome e introduza Ano.
Passo 6: agrupar linhas por IDDoProduto e Ano
Em Pré-visualização de Dados, selecione Ano e Order_Details.IDDoProduto.
Clique com o botão direito do rato num dos cabeçalhos e selecione Agrupar Por.
Na caixa de diálogo Agrupar por:
- Na caixa de texto Novo nome da coluna, digite Total de Vendas.
- Na caixa suspensa Operação, selecione Soma.
- Na caixa suspensa Coluna, selecione Total da Linha.
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 para a Tarefa 2
Após executar cada passo, tem uma consulta Total de Vendas no feed OData da Northwind.
Resumo: Power Query etapas criadas na Tarefa 2
À medida que executa atividades de consulta no Power Query, cria passos de consulta e apresenta-os no painel Definições de Consulta, na lista Passos Aplicados. 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 a documentação Power Query.
| Tarefa | Etapa de consulta | Fórmula |
|---|---|---|
| Conectar a um feed de OData | Origem | = OData.Feed("http://services.odata.org/Northwind/Northwind.svc", nulo, [Implementação="2.0"]) |
| Selecionar uma tabela | Navegação | = Source{[Name="Orders"]}[Dados] |
| 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(#"Expandir 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(ColunasRemovidas, "Personalizada", cada [Order_Details.PreçoUnitário] * [Order_Details.Quantidade]) = Table.AddColumn(#"Expandida Order_Details", "Total da Linha", cada [Order_Details.PreçoUnitário] * [Order_Details.Quantidade]) |
| Alterar para um nome mais significativo, Lne Total | Colunas Renomeadas | = Table.RenameColumns(InsertedCustom,{{"Custom", "Total da Linha"}}) |
| Transformar a coluna OrderDate para renderizar o ano | Ano extraído | = Table.TransformColumns(#"Linhas Agrupadas",{{"Year", Date.Year, Int64.Type}}) |
| Alterar para nomes mais significativos, DataDaEncomenda e Ano |
Colunas com nome alterado 1 |
Table.RenameColumns (TransformedColumn, {{"OrderDate", "Ano"}}) |
| Agrupar linhas por ID do Produto e Ano | GroupedRows | = Tabela.Grupo(ColunasComNomeMudado1, {"Ano", "Order_Details.IDDoProduto"}, {{"Total de Vendas", cada Soma.Lista([Total da Linha]), introduzir número}}) |
Tarefa 3: combinar as consultas de Produtos e Total de Vendas
Power Query permite-lhe combinar múltiplas consultas intercalando-as ou acrescentando às mesmas. Pode executar a operação Intercalar em qualquer consulta Power Query com uma forma de tabela, independentemente da origem de dados. Para obter mais informações sobre combinar fontes de dados, consulte Combinar várias consultas (Power Query).
Nesta tarefa, combina as consultas Produtos e Total de Vendas com uma consulta Intercalar e a operação Expandir e, em seguida, carrega a consulta Total de Vendas por Produto no Modelo de Dados do Excel.
Passo 1: intercalar IDDoProduto numa consulta Total de Vendas
No livro do Excel, aceda à consulta Produtos no separador da folha de cálculo Produtos.
Selecione uma célula na consulta e, em seguida, selecioneIntercalaçãode Consulta>.
Na caixa de diálogo Intercalar , selecione Produtos como a tabela principal e selecione Total de Vendas como a consulta secundária ou relacionada a intercalar. O Total de Vendas torna-se uma nova coluna estruturada com um ícone de expansão.
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.
Na caixa de diálogo Níveis de Privacidade:
- Selecione Organizacional para o seu nível de isolamento de privacidade para ambas as fontes de dados.
- Selecione Salvar.
Selecione OK.
Observação
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 (Power Query).
Resultado
A operação Intercalar cria uma consulta. O resultado da consulta contém todas as colunas da tabela principal (Produtos) e uma única coluna estruturada de Tabela para a tabela relacionada (Total de Vendas). Selecione o ícone Expandir para adicionar novas colunas à tabela principal a partir da tabela secundária ou relacionada.
Passo 2: expandir uma coluna unida
Neste passo, expande a coluna intercalada com o nome NovaColuna para criar duas colunas novas na consulta Produtos : Ano e Total de Vendas.
Em Pré-visualização de Dados, selecione o ícone Expandir (
) junto a NewColumn.Na lista pendente Expandir :
- Selecione (Selecionar Todas as Colunas) para limpar todas as colunas.
- Selecione Ano e Total de Vendas.
- Selecione OK.
Renomear essas duas colunas para Ano e Total de Vendas.
Para saber quais os produtos e em que anos os produtos obtiveram o maior volume de vendas, selecione Ordenar Descendente pelo Total de Vendas.
Renomear a consulta para Total de Vendas por Produto.
Resultado
Passo 3: carregar uma consulta Total de Vendas por Produto num Modelo de Dados do Excel
Neste passo, o utilizador carrega uma consulta para um Modelo de Dados do Excel para poder criar um relatório ligado ao resultado da consulta. Após carregar os dados para o Modelo de Dados do Excel, pode utilizar o Power Pivot para aprofundar a sua análise de dados.
- Selecione Página Inicial>,Fechar & Carregar.
- 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 OData Northwind. 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: Etapas do Power Query criadas na Tarefa 3
À medida que você executa 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 fórmulas do Power Query, consulte a documentação 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(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter) |
| Expandir uma coluna de mesclagem | Total de Vendas Expandido | = 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 o total de vendas em ordem crescente | Linhas Classificadas | = Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}}) |