Neste tutorial, pode utilizar a Editor de Consultas do Power Query para importar dados de um ficheiro do Excel local que contém informações do produto e de um feed OData que contém informações de encomenda de produtos. Efetua passos de transformação e agregação e combina dados de ambas as origens para produzir um relatório "Total de Vendas por Produto e Ano".
Para efetuar este tutorial, precisa do livro 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, 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
- Criar uma pasta de trabalho do Excel.
- Selecione Dados>Obter Dados>do Ficheiro>do Livro.
- Na caixa de diálogo Importar Dados , procure e localize o ficheiro Products.xlsx que transferiu e, em seguida, selecione Abrir.
- No painel Navegador , faça duplo clique na tabela Produtos . É apresentada a 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.
- Clique com o botão direito do rato no passo Origem e selecione Editar Definições. Este passo foi criado quando importou o livro.
- 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 .
- 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 colunas de interesse
Nesta etapa, você remove todas as colunas exceto ProductID, ProductName, CategoryID e QuantityPerUnit.
- Em Pré-visualização de Dados, selecione as colunas ProductID, ProductName, CategoryID e QuantityPerUnit (utilize Ctrl+Click ou Shift+Click).
- 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.
- > SelecioneFechar Base & 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 etapa da consulta tem uma fórmula correspondente do Power Query, 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.
| Tarefa | Etapa de 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 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, 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 um OrderDate, agrupar linhas por ProductID e Year, mudar o nome da consulta e desativar a transferência de consultas para o livro do Excel.
Passo 1: Ligar a um Feed OData
- Selecione Dados>Obter Dados>de Outras Origens>do Feed OData.
- Na caixa de diálogo Feed OData, digite a URL do feed OData da Northwind.
- Selecione OK.
- No painel Navegador , faça duplo clique na tabela Encomendas .
Passo 2: Expandir uma tabela de 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 (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 uma relaçã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.
Depois de expandir a tabela Order_Details , são adicionadas três novas colunas e linhas adicionais à tabela Encomendas , uma para cada linha na tabela aninhada ou relacionada.
Em Pré-visualização de Dados, desloque-se horizontalmente para 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 IDDoProduto, PreçoUnitário e Quantidade.
Selecione OK.
Observação
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 saber mais sobre como executar operações de agregação, consulte Agregar dados de uma coluna.
Passo 3: remover outras colunas para apresentar apenas colunas de interesse
Nesta etapa, você remove todas as colunas exceto DataPedido, ID do Produto, PreçoUnitário e Quantidade.
Em Pré-visualização de Dados, selecione as seguintes colunas:
- Selecione a primeira coluna, OrderID.
- Shift+Clique na última coluna, Transitário.
- 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 rato num cabeçalho de coluna selecionado e selecione Remover Outras Colunas.
Passo 4: Calcular o total da linha para cada linha Order_Details
Nesta etapa, você cria uma Coluna Personalizada para calcular o total de linhas de cada linha Order_Details.
- Em Pré-visualização de Dados, selecione o ícone de tabela (
) no canto superior esquerdo da pré-visualização. - Clique em Adicionar Coluna Personalizada.
- Na caixa de diálogo Coluna Personalizada , na caixa Fórmula de coluna personalizada , introduza [Order_Details.UnitPrice] * [Order_Details.Quantity].
- Na caixa Nome da nova coluna , introduza Total de Linhas.
- 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.
Em Visualização de Dados, clique com o botão direito do mouse na coluna OrderDate e selecione Transformar>Ano.
Renomeie a coluna DataPedido para Ano:
- Clique duas vezes na coluna DataPedido e digite Ano ou
- Right-Click na coluna OrderDate , selecione Renomear e insira Ano.
Etapa 6: Linhas de grupo por ProductID e Ano
Em Visualização de Dados, selecione Ano e Order_Details.ProductID.
Right-Click um dos cabeçalhos e selecione Grupo 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.
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}}) |
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 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 vendas totais
Na pasta de trabalho do Excel, navegue até a consulta Produtos na guia Planilha produtos .
Selecione uma célula na consulta e selecione Mesclagem de Consulta>.
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.
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.
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.
Em Visualização de Dados, selecione Expandir ícone (
) ao lado de NewColumn.Na lista suspensa Expandir :
- Selecione (Selecione Todas as Colunas) para limpar todas as colunas.
- Selecione Ano e Vendas Totais.
- Selecione OK.
Renomear essas duas colunas para Ano e Total de Vendas.
Para descobrir quais produtos e em quais anos os produtos receberam o maior volume de vendas, selecione Classificar Decrescente por Vendas Totais.
Renomear a consulta para Total de Vendas por Produto.
Resultado
Etapa 3: carregar um total de vendas por consulta de 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.
- Selecione Fechar inicial>& Carregar.
- 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 (?).
Resultado
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 etapa da consulta tem uma fórmula correspondente do Power Query, 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.
| 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 Expandidas | = Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"}) |
| Mudar o nome de duas colunas | Colunas Renomeadas | = Table.RenameColumns(#"Vendas Totais Expandidas",{{"Total de Vendas.Ano", "Ano"}, {"Total de Vendas.Total de Vendas", "Total de Vendas"}}) |
| Ordenar o total de Vendas por ordem ascendente | Linhas Classificadas | = Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}}) |