Criar, carregar ou editar uma consulta no Excel (Power Query)

Saber em que ambiente se encontra Power Query está bem integrada na interface de utilizador do Excel, especialmente quando importa dados, trabalha com ligações e edita Tabelas Dinâmicas, tabelas do Excel e intervalos com nome. Para evitar confusões, é importante saber em que ambiente se encontra atualmente, no Excel ou Power Query, a qualquer momento.

A folha de cálculo familiar do Excel, friso e grelha	O friso Editor do Power Query e a pré-visualização de dados

Por exemplo, manipular dados numa folha de cálculo do Excel é fundamentalmente diferente de Power Query. Além disso, os dados ligados que vê numa folha de cálculo do Excel podem ou não ter Power Query a trabalhar em segundo plano para formatar os dados. Isto só ocorre quando carrega os dados para uma folha de cálculo ou Modelo de Dados a partir de Power Query.

Mudar o nome dos separadores da folha de cálculo    Recomendamos que mude o nome dos separadores de folhas de cálculo de uma forma significativa, especialmente se tiver muitos. É particularmente importante esclarecer a diferença entre uma folha de cálculo de dados e uma folha de cálculo carregada a partir do Editor do Power Query. Mesmo que tenha apenas duas folhas de cálculo, uma com uma tabela do Excel, denominada Folha1, e outra uma consulta criada ao importar essa tabela do Excel, denominada Tabela1, é fácil confundir-se. É sempre uma boa prática alterar os nomes predefinidos dos separadores da folha de cálculo para nomes que façam mais sentido para si. Por exemplo, mude o nome da Folha1 para DataTable e Table1 para QueryTable. Agora, desmarque que separador tem os dados e que separador tem a consulta.

Pode criar uma consulta a partir de dados importados ou criar uma consulta em branco.

Criar uma consulta a partir de dados importados

Esta é a forma mais comum de criar uma consulta.

1. Importar alguns dados. Para obter mais informações, veja Importar dados de origens de dados externas.

2. Selecione uma célula nos dados e, em seguida, selecione Consulta > Editar.

Criar uma consulta em branco

Talvez queira começar do zero. Existem dois modos de o fazer.

• Selecione Dados > Obter > de Dadosde Outras Origens > Consulta em Branco.

• Selecione Dados > Obter Dados > Iniciar Editor do Power Query.

Neste momento, pode adicionar manualmente passos e fórmulas se souber bem a linguagem de fórmulas Power Query M.

Em alternativa, pode selecionar Base e, em seguida, selecionar um comando no grupo Nova Consulta . Efetue um dos seguintes procedimentos.

• Selecione Nova Origem para adicionar uma origem de dados. Este comando é semelhante ao comando Dados > Obter Dados no friso do Excel.

• Selecione Origens Recentes para selecionar a partir de uma origem de dados com a qual tenha trabalhado. Este comando é igual ao comando Data > Recent Sources no friso do Excel.

• Selecione Introduzir Dados para introduzir dados manualmente. Pode escolher este comando para experimentar o Editor do Power Query independentemente de uma origem de dados externa.

Partindo do princípio de que a consulta é válida e não tem erros, pode carregá-la novamente para uma folha de cálculo ou Modelo de Dados.

Carregar uma consulta a partir do Editor do Power Query

Na Editor do Power Query, efetue um dos seguintes procedimentos:

• Para carregar para uma folha de cálculo, selecione Base > Fechar & Carregar > Fechar & Carregar.

• Para carregar para um Modelo de Dados , selecione Base> Fechar & Carregar > Fechar & Carregar Para.
  
  Na caixa de diálogo Importar Dados , selecione Adicionar estes dados ao Modelo de Dados.

Sugestão   Por vezes, o comando Carregar Para está desativado ou desativado. Isto pode ocorrer quando cria uma consulta num livro pela primeira vez. Se isto ocorrer, selecione Fechar & Carregar, na nova folha de cálculo, selecione Dados > Consultas & Connections > separador Consultas, clique com o botão direito do rato na consulta e, em seguida, selecione Carregar Para. Em alternativa, no friso Editor do Power Query, selecione Consulta > Carregar Para.

Carregar uma consulta a partir do painel Consultas e Connections 

No Excel, poderá querer carregar uma consulta para outra folha de cálculo ou Modelo de Dados.

1. No Excel, selecione Dados > Consultas & Connections e, em seguida, selecione o separador Consultas.

2. Na lista de consultas, localize a consulta, clique com o botão direito do rato na consulta e, em seguida, selecione Carregar Para. A caixa de diálogo Importar Dados aparece.

3. Decida como pretende importar os dados e, em seguida, selecione OK. Para obter mais informações sobre como utilizar esta caixa de diálogo, selecione o ponto de interrogação (?).

Existem várias formas de editar uma consulta carregada para uma folha de cálculo.

Editar uma consulta a partir de dados na folha de cálculo do Excel

• Para editar uma consulta, localize uma que foi carregada anteriormente a partir do Editor do Power Query, selecione uma célula nos dados e, em seguida, selecione Consulta > Editar.

Editar uma consulta a partir do painel consultas & Connections

Poderá considerar que o painel Consultas & Connections é mais conveniente de utilizar quando tem muitas consultas num livro e quer encontrar rapidamente uma.

1. No Excel, selecione Dados > Consultas & Connections e, em seguida, selecione o separador Consultas.

2. Na lista de consultas, localize a consulta, clique com o botão direito do rato na consulta e, em seguida, selecione Editar.

Editar uma consulta a partir da caixa de diálogo Propriedades da Consulta

• No Excel, selecione Dados > Dados & Connections > separador Consultas, clique com o botão direito do rato na consulta e selecione Propriedades, selecione o separador Definição na caixa de diálogo Propriedades e, em seguida, selecione Editar Consulta.

Sugestão    Se estiver numa folha de cálculo com uma consulta, selecione Dados > Propriedades, selecione o separador Definição na caixa de diálogo Propriedades e, em seguida, selecione Editar Consulta. 

Normalmente, um Modelo de Dados contém várias tabelas dispostas numa relação. Carrega uma consulta para um Modelo de Dados com o comando Carregar Para para apresentar a caixa de diálogo Importar Dados e, em seguida, seleciona a caixa de verificação Adicionar estes dados ao Modo de Dadosl. Para obter mais informações sobre Modelos de Dados, consulte Descobrir que origens de dados são utilizadas num modelo de dados de livro, Criar um Modelo de Dados no Excel e Utilizar várias tabelas para criar uma Tabela Dinâmica.

1. Para abrir o Modelo de Dados, selecione Power Pivot > Gerir.

2. Na parte inferior da janela do Power Pivot, selecione o separador da folha de cálculo da tabela que pretende.
   
   Confirme que a tabela correta é apresentada. Um Modelo de Dados pode ter muitas tabelas.

3. Anote o nome da tabela.

4. Para fechar a janela do Power Pivot, selecione Ficheiro > Fechar. A recuperação da memória poderá demorar alguns segundos.

5. Selecione Dados > Connections & propriedades > separador Consultas, clique com o botão direito do rato na consulta e, em seguida, selecione Editar.

6. Quando terminar de fazer alterações no Editor do Power Query, selecione Ficheiro > Fechar & Carregar.

Result

A consulta na folha de cálculo e a tabela no Modelo de Dados são atualizadas.

Se reparar que o carregamento de uma consulta para um Modelo de Dados demora muito mais tempo do que o carregamento para uma folha de cálculo, verifique os passos Power Query para ver se está a filtrar uma coluna de texto ou uma coluna estruturada de Lista com um operador Contém. Esta ação faz com que o Excel volte a enumerar através de todo o conjunto de dados para cada linha. Além disso, o Excel não pode utilizar efetivamente a execução multithreaded. Como solução, experimente utilizar um operador diferente, como É Igual ou Começa Com.

A Microsoft tem conhecimento deste problema e está a ser investigada.

Pode carregar uma Power Query:

• Para uma folha de cálculo. Na Editor do Power Query, selecione Base > Fechar & Carregar > Fechar & Carregar.

• Para um Modelo de Dados. Na Editor do Power Query, selecione Base > Fechar & Carregar > Fechar & CarregarPara. 
  
  Por predefinição, Power Query carrega consultas para uma nova folha de cálculo ao carregar uma única consulta e carrega várias consultas ao mesmo tempo para o Modelo de Dados.  Pode alterar o comportamento predefinido de todos os seus livros ou apenas do livro atual. Ao definir estas opções, Power Query não altera os resultados da consulta na folha de cálculo nem nos dados e anotações do Modelo de Dados.
  
  Também pode substituir dinamicamente as predefinições de uma consulta através da caixa de diálogo Importar que é apresentada depois de selecionar Fechar & Carregar Para.

Definições globais que se aplicam a todos os seus livros

1. No Power Editor do Power Query, selecione Opções de> de Ficheiros e definições > Opções de Consulta.

2. Na caixa de diálogo Opções de Consulta , no lado esquerdo, na secção GLOBAL , selecione Carregamento de Dados.

3. Na secção Predefinições de Carregamento da Consulta , faça o seguinte:

   • Selecione Utilizar definições de carga padrão. 

   • Selecione Especificar predefinições de carga predefinidas personalizadas e, em seguida,  selecione ou desmarque Carregar para a folha de cálculo ou Carregar para o Modelo de Dados.

Sugestão    Na parte inferior da caixa de diálogo, pode selecionar Restaurar Predefinições para voltar convenientemente às predefinições.

Definições do livro que se aplicam apenas ao livro atual

1. Na caixa de diálogo Opções de Consulta , no lado esquerdo, na secção LIVRO ATUAL, selecione Carregamento de Dados.

2. Efetue um ou mais dos seguintes procedimentos:

   • Em Deteção de Tipos, selecione ou desmarque Detetar tipos de coluna e cabeçalhos para origens não estruturadas.
     
     O comportamento predefinido é detetá-los. Desmarque esta opção se preferir formatar os dados manualmente.

   • Em Relações, selecione ou desmarque Criar relações entre tabelas ao adicionar ao Modelo de Dados pela primeira vez.

     Antes de carregar para o Modelo de Dados, o comportamento predefinido é localizar relações existentes entre tabelas, como chaves externas numa base de dados relacional e importá-las com os dados. Desmarque esta opção se preferir fazê-lo sozinho.

   • Em Relações, selecione ou desmarque Atualizar relações ao atualizar as consultas carregadas para o Modelo de Dados.
     
     O comportamento predefinido é não atualizar relações. Ao atualizar as consultas já carregadas para o Modelo de Dados, Power Query localiza as relações existentes entre tabelas, como chaves externas numa base de dados relacional, e atualiza-as. Isto pode remover relações criadas manualmente após a importação dos dados ou introduzir novas relações. No entanto, se quiser fazê-lo, selecione a opção .

   • Em Dados em Segundo Plano, selecione ou desmarque Permitir a transferência de pré-visualizações de dados em segundo plano.
     
     O comportamento predefinido é transferir pré-visualizações de dados em segundo plano. Desmarque esta opção se quiser ver todos os dados imediatamente.