Combinar dados a partir de múltiplas origens de dados (Power Query)

Nota:  Queremos fornecer-lhe os conteúdos de ajuda mais recentes o mais rapidamente possível e no seu idioma. Esta página foi traduzida automaticamente e pode conter erros gramaticais ou imprecisões. O nosso objetivo é que estes conteúdos lhe sejam úteis. Pode informar-nos se as informações foram úteis no final desta página? Eis o artigo em inglês para referência.

Nota: Power Query é conhecido como obter e transformar no Excel 2016. Informações fornecidas aqui aplicam-se para ambas. Para saber mais, consulte o artigo obter e transformar no Excel 2016.

Neste tutorial, irá utilizar o Editor de consultas do Power Query para importar dados a partir de um ficheiro local do Excel que contém informações de produto e de um feed que OData contém informação da encomenda produto. Execute os passos da transformação e agregação e combinar dados a partir de ambas as origens para produzir um relatório de Total de vendas por produto e ano.

Para realizar este tutorial, é necessário o livro de produtos e encomendas . Na caixa de diálogo Guardar como, dê um nome do ficheiro produtos e encomendas. xlsx.

Neste tutorial:

Tarefa 1: importar produtos para um livro do Excel

Passo 1: ligar a um livro do Excel

Passo 2: promover a primeira linha a cabeçalhos de coluna de tabela

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

Passos criados pelo Power Query

Passo 4: importar uma consulta de produtos

Tarefa 2: importar dados de encomenda de um feed OData

Passo 1: ligar a um feed OData

Passo 2: expandir uma tabela Detalhes_Encomenda

Expandir a ligação Tabela Detalhes_Encomenda

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

Remover as colunas selecionadas

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

Calcular o total da linha para cada linha Detalhes_Encomenda

Passo 5: transformar uma coluna de ano DataDaEncomenda

Passo 6: agrupar linhas por IDDoProduto e Ano

Passo 7: mudar o nome de uma consulta

Resultados da consulta final

Passos criados pelo Power Query

Passo 8: desativar a transferência de uma consulta para um livro do Excel

Desativar a transferência de uma consulta

Tarefa 3: combinar as consultas Produtos e Total de Vendas

Passo 1: intercalar IDDoProduto numa consulta Total de Vendas

Passo 2: Expandir uma coluna de intercalação

Expandir uma ligação de tabela NovaColuna

Passos criados pelo Power Query

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

Carregar uma consulta Total de Vendas por Produto no Modelo de Dados do Excel

Consulta Total de Vendas Finais por Produto

Tarefa 1: importar produtos para um livro do Excel

Nesta tarefa, irá importar produtos do ficheiro produtos e encomendas. xlsx para um livro do Excel.

Passo 1: ligar a um livro do Excel

  1. Crie um livro do Excel.

  2. No separador POWER QUERY do friso, clique em De Ficheiro > Do Excel.

  3. Na caixa de diálogo procurar do Excel, procure ou escreva o caminho do ficheiro Produtos e Encomendas.xlsx para importar ou ligar a um ficheiro.

  4. No painel Navegador, faça duplo-clique na folha de cálculo Produtos ou clique em Produtos e clique em Editar Consulta. Quando edita uma consulta ou liga a uma nova origem de dados, é apresentada a janela do Editor de Consultas.

    Nota: Para obter um vídeo rápido sobre como apresentar o Editor de Consultas, consulte a parte final deste artigo.

Passo 2: promover a primeira linha a cabeçalhos de coluna de tabela

Na grelha Pré-visualização da Consulta, a primeira linha da tabela não contém os nomes da coluna da tabela. Promover a primeira linha a cabeçalhos de coluna de tabela:

  1. Clique no ícone de tabela ( Ícone de tabela ) no canto superior esquerdo da pré-visualização dos dados.

  2. Clique em Utilizar Primeira Linha como Cabeçalho.

Promover a primeira linha a cabeçalhos de coluna de tabela

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. Na grelha Pré-visualização da Consulta, selecione as colunas IDDoProduto, NomeDoProduto, IDDaCategoria e QuantidadePorUnidade (utilizando Ctrl+Clique ou Shift+Clique).

  2. No friso Editor de Consultas, clique em Remover Colunas > Remover Outras Colunas ou clique com o botão direito do rato no cabeçalho de uma coluna e clique em Remover Outras Colunas.

    Ocultar outras colunas

Passos criados pelo Power Query

À medida que desempenha atividades de consulta no Power Query, os passos da consulta são criados e apresentados 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 a linguagem de fórmulas do Power Query, consulte o artigo Saiba mais sobre fórmulas do Power Query.

Tarefas

Passo da consulta

Fórmula

Ligar a um livro do Excel

Origem

Source{[Name="Produtos"]}[Dados]

Promover a primeira linha a cabeçalhos de coluna de tabela

PrimeiraLinhaComoCabeçalho

Tabela.PromoverCabeçalhos

(Produtos)

Remover outras colunas para apresentar apenas as colunas de interesse

OutrasColunasRemovidas

Tabela.SelecionarColunas

(PrimeiraLinhaComoCabeçalho,{"IDDoProduto", "NomeDoProduto", "IDDaCategoria", "QuantidadePorUnidade"})

Passo 4: importar uma consulta de produtos

Neste passo, o utilizador importa a consulta Produtos para o respetivo livro do Excel.

  1. No friso Editor de Consultas, clique em Aplicar e Fechar. Os resultados são apresentados numa nova folha de cálculo do Excel.

Início da Página

Tarefa 2: importar dados de encomenda de um feed OData

Nesta tarefa, irá importar dados para um livro do Excel do exemplo feed OData da Northwind em http://services.odata.org/Northwind/Northwind.svc.

Passo 1: ligar a um feed OData

  1. No separador POWER QUERY do friso, clique em De Outras Origens > De Feed de OData.

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

  3. Clique em OK.

  4. No painel Navegador, faça duplo clique na tabela Encomendas ou clique em Encomendas e clique em Editar.

Nota: Ao pairar com o cursor do seu rato numa tabela, verá uma pré-visualização da lista de opções da tabela.

Paire com o cursor do rato sobre uma Origem de Dados

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 uma consulta é executada, as linhas da tabela relacionada (Detalhes_Encomenda) são combinadas em linhas da tabela de assunto (Encomendas).

No Power Query, uma coluna com uma ligação para uma tabela relacionada inclui uma ligação Entrada ou uma ligação Tabela. Uma ligação Entrada navega para um único registo relacionado e representa umarelação um-para-um com uma tabela de assunto. Uma ligação Tabela navega para uma tabela relacionada e representa uma relação um-para-muitos com uma tabela de assunto. Uma ligação representa as propriedades de navegação numa origem de dados num modelo relacional. Para um feed OData, as propriedades de navegação representam uma entidade com uma associação de chave externa. Numa base de dados, como SQL Server, as propriedades da navegação representam relações de chave externa na base de dados.

Expandir a ligação Tabela Detalhes_Encomenda

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. No painel Pré-visualização da Consulta, desloque para a coluna Detalhes_Encomenda.

  2. Na coluna detalhes_encomenda, clique no ícone de expansão ( Expandir ).

  3. No menu pendente Expandir:

    1. Clique em (Selecionar Todas as Colunas) para desmarcar todas as colunas.

    2. Clique em IDDoProduto, PreçoUnitário e Quantidade.

    3. Clique em OK.

      Expandir a ligação Tabela Detalhes_Encomenda

      Nota:  No Power Query, pode expandir tabelas ligadas de uma coluna e tem também a capacidade de executar operações de agregação nas 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. Na tarefa anterior, utilizou a opção Remover Outras Colunas. Para esta tarefa, irá remover as colunas selecionadas.

Remover as colunas selecionadas

  1. No painel Pré-visualização da Consulta, selecione todas as colunas:

    1. Clique na primeira coluna (IDDaEncomenda).

    2. Prima 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 clique em Remover 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.

Calcular o total da linha para cada linha Detalhes_Encomenda

  1. No painel de Pré-visualização de consulta, clique no ícone de tabela ( Ícone de tabela ) no canto superior esquerdo da pré-visualização.

  2. Clique em Inserir Coluna > Personalizada.

  3. Na caixa de diálogo Inserir Coluna Personalizada, na caixa de texto Fórmula da Coluna Personalizada, introduza [Detalhes_Encomenda.PreçoUnitário] * [Detalhes_Encomenda.Quantidade].

  4. Na caixa de texto Novo nome de coluna, introduza Total da Linha.

  5. Clique em OK.

Calcular o total da linha para cada linha Detalhes_Encomenda

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. Na grelha Pré-visualização, clique com botão direito do rato na coluna DataDaEncomenda e clique em Transformar > Ano.

  2. Mude o nome da coluna DataDaEncomenda para Ano:

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

    2. Clique com o botão direito do rato na coluna DataDaEncomenda, clique em Mudar o Nome e introduza Ano.

Passo 6: agrupar linhas por IDDoProduto e Ano

  1. Na grelha Pré-visualização da Consulta, selecione Ano e Detalhes_Encomenda.IDDoProduto.

  2. Clique com o botão direito do rato num dos cabeçalhos e clique em 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. Clique em OK.

    Caixa de Diálogo Agrupar Por para Operações de Agregação

Passo 7: mudar o nome de uma consulta

Antes de importar os dados de vendas para o Excel, dê o nome Total de Vendas à consulta:

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

Resultados da consulta final

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

Total de Vendas

Passos criados pelo Power Query

À medida que desempenha atividades de consulta no Power Query, os passos da consulta são criados e apresentados 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 a linguagem de fórmulas do Power Query, consulte o artigo Saiba mais sobre fórmulas do Power Query.

Tarefas

Passo da consulta

Fórmula

Ligar a um feed OData

Origem

Source{[Name="Encomendas"]}[Dados]

Expandir a tabela Detalhes_Encomenda

Expandir Detalhes_Encomenda

Tabela.ExpandirColunaDaTabela

(Orders, "Detalhes_Encomenda", {"IDDoProduto", "PreçoUnitário", "Quantidade"}, {"Detalhes_Encomenda.IDDoProduto", "Detalhes_Encomenda.PreçoUnitário", "Detalhes_Encomenda.Quantidade"})

Remover outras colunas para apresentar apenas as colunas de interesse

ColunasRemovidas

Tabela.RemoverColunas

(#"Expandir Detalhes_Encomenda",{"IDDaEncomenda", "IDDoCliente", "IDDoFuncionário", "DataRequerida", "DataDeEnvio", "EnviarPor", "Carga", "NomeDoEnvio", "EndereçoDeEnvio", "CidadeDeEnvio", "RegiãoDeEnvio", "CódigoPostalDeEnvio", "PaísDeEnvio", "Cliente", "Empregado", "Transitário"})

Calcular o total da linha para cada linha Detalhes_Encomenda

ColunasInseridas

Tabela.AdicionarColuna

(ColunasRemovidas, "Personalizada", cada [Detalhes_Encomenda.PreçoUnitário] * [Detalhes_Encomenda.Quantidade])

Transformar a coluna DataDaEncomenda para compor o ano

ColunasComNomeMudado

Tabela.MudarONomeDeColunas

(PersonalizadaInserida,{{"Personalizada", "Total da Linha"}})

ColunaTransformada

Tabela.TransformarColunas

(ColunasComNomeMudado,{{"DataDaEncomenda", Data.Ano}})

ColunasComNomeMudado1

Tabela.MudarONomeDeColunas

(ColunaTransformada,{{"DataDaEncomenda", "Ano"}})

Agrupar linhas por IDDoProduto e Ano

LinhasAgrupadas

Tabela. grupo
(Colunascomnomemudado1, {"Ano", "Detalhes_encomenda. iddoproduto"} {{"Total de vendas", cada soma. lista ([Total da linha]), introduzir número}})

Passo 8: desativar a transferência de uma consulta para um livro do Excel

Uma vez que a consulta Total de Vendas não representa o relatório Total de Vendas por Produto e Ano final, o utilizador desativa a transferência da consulta para o livro do Excel. Quando a opção Carregar para Folha de Cálculo está Desativada no painel Definições da consulta, o resultado de dados desta consulta não é transferido mas a consulta pode ainda ser combinada com outras consultas, de modo a criar o resultado pretendido. Aprende a combinar esta consulta com a consulta Produtos na tarefa seguinte.

Desativar a transferência da consulta

  1. No painel Definições da Consulta desmarque Carregar para folha de cálculo.

  2. No friso Editor de Consultas, clique em Aplicar e Fechar. No painel Consultas ao Livro, a consulta Total de Vendas apresenta O carregamento está desativado.

    Desativar a transferência de uma consulta

Início da Página

Tarefa 3: combinar as consultas Produtos e Total de Vendas

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, o utilizador combina as consultas Produtos e Total de Vendas utilizando os passos de consulta Intercalar e Expandir.

Passo 1: intercalar IDDoProduto numa consulta Total de Vendas

  1. No livro do Excel, navegue para a consulta Produtos na Folha2.

  2. No separador CONSULTA do friso, clique em Intercalar.

  3. 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 será uma nova coluna expansível.

  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. Clique em Guardar.

  6. Clique em 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 mais informações sobre os Níveis de Privacidade, consulte o artigo Níveis de Privacidade.

    Caixa de diálogo Intercalar

Após clicar em OK, a operação Intercalar cria uma consulta. O resultado da consulta contém todas as colunas da tabela principal (Produtos) e uma coluna única que contém uma ligação de navegação para a tabela relacionada (Total de Vendas). Uma operação Expandir adiciona novas colunas na tabela principal ou de assunto a partir de uma tabela relacionada.

Intercalação Final

Passo 2: expandir uma coluna de intercalação

Neste passo, o utilizador expande a coluna de intercalação com o nome Novacoluna para criar duas novas colunas na consulta produtos: ano e Total de vendas.

Expandir uma ligação de tabela NovaColuna

  1. Na grelha de Pré-visualização de consulta, clique na Novacoluna expanda ícone ( Expandir ).

  2. No menu pendente Expandir:

    1. Clique em (Selecionar Todas as Colunas) para desmarcar todas as colunas.

    2. Clique em Ano e em Total de Vendas.

    3. Clique em OK.

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

  4. Selecione a Ordenação Descendente por Total de Vendas para saber quais os produtos e em que anos os produtos obtiveram o volume mais alto de vendas.

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

Ligação Expandir tabela

Passos criados pelo Power Query

À medida que executa atividades de consulta Intercalar no Power Query, os passos de consulta são criados e apresentados 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 a linguagem de fórmulas do Power Query, consulte o artigo Saiba mais sobre fórmulas do 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)

Tabela.AssociaçãoAninhada

(Produtos,{"IDDoProduto"},#"Total de Vendas",{"Detalhes_Encomenda.IDDoProduto"},"NovaColuna")

Expandir uma coluna de intercalação

ExpandirNovaColuna

Tabela.ExpandirColunaDaTabela

(Fonte, "NovaColuna", {"Ano", "Vendas Totais"}, {"NovaColuna.Ano", "NovaColuna.Total de Vendas"})

ColunasComNomeMudado

Tabela.MudarONomeDeColunas

(#"Expandir NovaColuna",{{"NovaColuna.Ano", "Ano"}, {"NovaColuna.Total de Vendas", "Total de Vendas"}})

LinhasOrdenadas

Tabela.Ordenar

(ColunasComNomeMudado,{{"Total de Vendas", Ordem.Descendente}})

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

Neste passo, o utilizador desativa a opção Carregar para a Folha de Cálculo e carrega uma consulta para um Modelo de Dados do Excel de modo a criar uma relatório ligado ao resultado da consulta. Para além de carregar resultados da consulta numa folha de cálculo do Excel, o Power Query permite-lhe carregar um resultado da consulta para um Modelo de Dados do Excel. Após carregar os dados para um Modelo de Dados do Excel, pode utilizar o PowerPivot e o Power View para obter uma análise mais aprofundada.

Carregar uma consulta Total de Vendas por Produto no Modelo de Dados do Excel

  1. No painel Definições da Consulta, desmarque Carregar para a folha de cálculo e selecione Carregar para modelo de dados.

  2. Para carregar a consulta num Modelo de Dados do Excel, clique em Aplicar e Fechar.

Carregar Modelo de Dados do Excel

Consulta Total de Vendas Finais por Produto

Após executar cada passo, terá uma consulta Total de Vendas por Produto que combina dados do ficheiro Produtos e Encomendas.xlsx e o feed OData da Northwind. Esta consulta pode ser aplicada a um modelo do PowerPivot. Para além disso, as alterações à consulta no Power Query modificam e atualizam a tabela resultante no modelo do PowerPivot.

Início da Página

Nota: O Editor de consultas só aparece quando carrega, editar ou criar uma nova consulta utilizando o Power Query. O vídeo seguinte mostra a janela do Editor de consultas a aparecer depois de editar uma consulta a partir de um livro do Excel. Para ver o Editor de consultas sem a ser carregados ou editar uma consulta existente do livro, a partir da secção de Obter dados externos no separador do friso Power Query, selecione de outras origens > consulta em branco. O vídeo seguinte mostra uma forma de apresentar o Editor de consultas.

Como ver o Editor de Consultas no Excel

Aumente os seus conhecimentos do Office
Explore as formações
Seja o primeiro a obter novas funcionalidades
Adira ao Office Insider

As informações foram úteis?

Obrigado pelos seus comentários!

Obrigado pelo seu feedback! Parece que poderá ser benéfico reencaminhá-lo para um dos nossos agentes de suporte do Office.

×