Applies ToExcel para Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016 Excel 2013

Ao utilizar o Editor do Power Query, tem vindo sempre a criar fórmulas Power Query. Vamos ver como funciona Power Query ao olhar para debaixo dos bastidores. Pode saber como atualizar ou adicionar fórmulas apenas ao ver o Editor do Power Query em ação.  Pode até implementar as suas próprias fórmulas com o Editor Avançado.           

O Editor do Power Query fornece uma consulta de dados e uma experiência de formatação para o Excel que pode utilizar para reformular dados de muitas origens de dados. Para apresentar a janela Editor do Power Query, importe dados de origens de dados externasnuma folha de cálculo do Excel, selecione uma célula nos dados e, em seguida, selecione Consulta > Editar. Segue-se um resumo dos componentes principais.

Peças do Editor de Consultas

  1. O friso Editor do Power Query que utiliza para formatar os seus dados

  2. O painel Consultas que utiliza para localizar origens de dados e tabelas

  3. Menus de contexto que são atalhos convenientes para comandos no friso

  4. A Pré-visualização de Dados que apresenta os resultados dos passos aplicados aos dados

  5. O painel Definições da Consulta que lista as propriedades e cada passo na consulta

Nos bastidores, cada passo numa consulta baseia-se numa fórmula que é visível na barra de fórmulas.

Amostra de Fórmula do Editor de Consultas

Pode haver alturas em que pretende modificar ou criar uma fórmula. As fórmulas utilizam o Power Query Linguagem de Fórmulas, que pode utilizar para criar expressões simples e complexas. Para obter mais informações sobre sintaxe, argumentos, observações, funções e exemplos, veja Power Query linguagem de fórmulas M.

Utilizando uma lista de campeonatos de futebol como exemplo, utilize Power Query para obter dados não processados que encontrou num site e transformá-lo numa tabela bem formatada. Veja como os passos de consulta e as fórmulas correspondentes são criados para cada tarefa no painel Definições da Consulta em Passos Aplicados e na Barra de fórmulas.

O seu browser não suporta vídeo. Instale o Microsoft Silverlight, o Adobe Flash Player ou o Internet Explorer 9.

Procedimento

  1. Para importar os dados, selecione Dados > Da Web, introduza "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" na caixa URL e, em seguida, selecione OK.

  2. Na caixa de diálogo Navegador , selecione a tabela Resultados [Editar] à esquerda e, em seguida, selecione Transformar Dados na parte inferior. É apresentado o editor de Power Query.

  3. Para alterar o nome da consulta predefinido, no painel Definições da Consulta , em Propriedades, elimine "Resultados [Editar]" e, em seguida, introduza "Campeões da UEFA".

  4. Para remover colunas indesejadas, selecione a primeira, quarta e quinta colunas e, em seguida, selecione Base > Remover Coluna > Remover Outras Colunas.

  5. Para remover valores indesejados, selecione Coluna1, selecione Base > Substituir Valores, introduza "detalhes" na caixa Valores a Localizar e, em seguida, selecione OK.

  6. Para remover linhas com a palavra "Ano", selecione a seta de filtro na Coluna1, desmarque a caixa de verificação junto a "Ano" e, em seguida, selecione OK.

  7. Para mudar o nome dos cabeçalhos de coluna, faça duplo clique em cada um deles e, em seguida, altere "Column1" para "Year", "Column4" para "Winner" e "Column5" para "Final Score".

  8. Para guardar a consulta, selecione Base > Fechar & Carregar.

Result

Resultados das instruções – as primeiras linhas

A tabela seguinte é um resumo de cada passo aplicado e da fórmula correspondente.

Passo e tarefa da consulta

Fórmula

Origem

Ligar a uma origem de dados Web

= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))

Navegação

Selecione a tabela a ligar

=Source{2}[Data]

Tipo Alterado

Alterar tipos de dados (o que Power Query faz automaticamente)

= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})

Outras Colunas Removidas

Remover outras colunas para apresentar apenas as colunas de interesse

= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})

Valor Substituído

Substituir valores para limpar valores numa coluna selecionada

= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})

Linhas Filtradas

Filtrar valores numa coluna

= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))

Colunas com Nome Mudado

Os cabeçalhos de coluna foram alterados para serem significativos

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Importante    Tenha cuidado ao editar os passos Origem, Navegação  e Tipo Alterado porque são criados por Power Query para definir e configurar a origem de dados.

Mostrar ou ocultar a barra de fórmulas

A barra de fórmulas é apresentada por predefinição, mas se não estiver visível, pode voltar a reproduzi-la.

  • Selecione Ver > Esquema > Barra de Fórmulas.

Edit uma fórmula na barra de fórmulas

  1. Para abrir 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. Para obter mais informações , consulte Criar, carregar ou editar uma consulta no Excel.

  2. No painel Definições da Consulta , em Passos Aplicados, selecione o passo que pretende editar.

  3. Na barra de fórmulas, localize e altere os valores dos parâmetros e, em seguida, selecione o ícone Enter O ícone Enter à esquerda da barra de fórmulas no Power Query ou prima Enter. Por exemplo, altere esta fórmula para manter também Column2:Before: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})After:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. Selecione o ícone Introduzir O ícone Enter à esquerda da barra de fórmulas no Power Query ou prima Enter para ver os novos resultados apresentados na Pré-visualização de Dados.

  5. Para ver o resultado numa folha de cálculo do Excel, selecione Base > Fechar & Carregar.

Criar uma fórmula na barra de fórmulas

Para um exemplo de fórmula simples, vamos converter um valor de texto em maiúsculas/minúsculas com a função Text.Proper.

  1. Para abrir uma consulta em branco, no Excel, selecione Dados > Obter Dados > De Outras Origens > Consulta em Branco. Para obter mais informações , consulte Criar, carregar ou editar uma consulta no Excel.

  2. Na barra de fórmulas, introduza=Text.Proper("text value")e, em seguida, selecione o ícone Enter O ícone Enter à esquerda da barra de fórmulas no Power Query ou prima Enter.Os resultados são apresentados na Pré-visualização de Dados.

  3. Para ver o resultado numa folha de cálculo do Excel, selecione Base > Fechar & Carregar.

Resultado:

Selecione a animação que pretende acionar

 Quando cria uma fórmula, Power Query valida a sintaxe da fórmula. No entanto, quando insere, reordena ou elimina um passo intermédio numa consulta, pode potencialmente interromper uma consulta.  Verifique sempre os resultados na Pré-visualização de Dados.

Importante    Tenha cuidado ao editar os passos Origem, Navegação  e Tipo Alterado porque são criados por Power Query para definir e configurar a origem de dados.

Editar uma fórmula com uma caixa de diálogo

Este método utiliza caixas de diálogo que variam consoante o passo. Não precisa de saber a sintaxe da fórmula.

  1. Para abrir 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. Para obter mais informações , consulte Criar, carregar ou editar uma consulta no Excel.

  2. No painel Definições da Consulta , em Passos Aplicados, selecione o ícone Editar Definições Ícone Definições do passo que pretende editar ou clique com o botão direito do rato no passo e, em seguida, selecione Editar Definições.

  3. Na caixa de diálogo, faça as alterações e, em seguida, selecione OK.

Inserir um passo

Depois de concluir um passo de consulta que reformula os seus dados, é adicionado um passo de consulta abaixo do passo de consulta atual. mas quando insere um passo de consulta no meio dos passos, pode ocorrer um erro nos passos subsequentes. Power Query apresenta um aviso Inserir Passo quando tenta inserir um novo passo e o novo passo altera os campos, como os nomes das colunas, que são utilizados em qualquer um dos passos que se seguem ao passo inserido.

  1. No painel Definições da Consulta , em Passos Aplicados, selecione o passo que pretende preceder imediatamente o novo passo e a fórmula correspondente.

  2. Selecione o ícone Adicionar Passo Ícone Função à esquerda da barra de fórmulas. Em alternativa, clique com o botão direito do rato num passo e, em seguida, selecione Inserir Passo Após. É criada uma nova fórmula no formato := <nameOfTheStepToReference>, como =Production.WorkOrder.

  3. Escreva a nova fórmula com o formato:=Class.Function(ReferenceStep[,otherparameters]) Por exemplo, suponha que tem uma tabela com a coluna Género e pretende adicionar uma coluna com o valor "Ms". ou "Sr.", dependendo do sexo da pessoa. A fórmula seria:=Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Fórmula de exemplo

Reordenar um passo

  • No painel Definições das Consultas , em Passos Aplicados, clique com o botão direito do rato no passo e, em seguida, selecione Mover Para Cima ou Mover Para Baixo.

Elimine o passo

  • Selecione o ícone Eliminar Elimine o passo à esquerda do passo ou clique com o botão direito do rato no passo e, em seguida, selecione Eliminar ou Eliminar Até Terminar. O ícone Eliminar Elimine o passo também está disponível à esquerda da barra de fórmulas.

Neste exemplo, vamos converter o texto numa coluna em maiúsculas/minúsculas com uma combinação de fórmulas no Editor Avançado. 

Por exemplo, tem uma tabela do Excel, denominada Encomendas, com uma coluna ProductName que pretende converter em maiúsculas/minúsculas. 

Antes:

Fluxograma com os pontos de ligação vermelhos.

Depois:

Passo 4 - Resultado

Quando cria uma consulta avançada, cria uma série de passos de fórmulas de consulta com base na expressão let. Utilize a expressão let para atribuir nomes e calcular valores que são depois referenciados pela cláusula in , que define o Passo. Este exemplo devolve o mesmo resultado que o da secção "Criar uma fórmula na barra de fórmulas".

let       Source = Text.Proper("hello world") in       Source  

Verá que cada passo se baseia num passo anterior ao referir-se a um passo por nome. Como lembrete, o Power Query Linguagem de Fórmulas é sensível às maiúsculas e minúsculas.

Fase 1: Abrir o Editor Avançado

  1. No Excel, selecione Dados > Obter Dados > Outras Origens > Consulta em Branco. Para obter mais informações , consulte Criar, carregar ou editar uma consulta no Excel.

  2. Na Editor do Power Query, selecione Base > Editor Avançado, que é aberto com um modelo da expressão let.

Relatório de empregados em Pré-visualizar

Fase 2: Definir a origem de dados

  1. Crie a expressão let com a função Excel.CurrentWorkbook da seguinte forma:let    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]in      Source#x4

  2. Para carregar a consulta para uma folha de cálculo, selecione Concluído e , em seguida , selecione Base> Fechar & Carregar > Fechar & Carregar.

Resultado:

Símbolo matemático

Fase 3: Promover a primeira linha a cabeçalhos

  1. Para abrir a consulta, na folha de cálculo, selecione uma célula nos dados e, em seguida, selecione Consulta > Editar. Para obter mais informações, consulte Criar, carregar ou editar uma consulta no Excel (Power Query).

  2. Na Editor do Power Query, selecione Base > Editor Avançado, que é aberta com a instrução que criou na Fase 2: Definir a origem de dados.

  3. Na expressão let, adicione #"Primeira Linha como Cabeçalho" e a função Table.PromoteHeaders da seguinte forma:let      Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],        #"First Row as Header"#x3#"First Row as Header" = Table.PromoteHeaders(Source)

  4. Para carregar a consulta para uma folha de cálculo, selecione Concluído e , em seguida , selecione Base> Fechar & Carregar > Fechar & Carregar.

Resultado:

Passo 3 - Resultado

Fase 4: Alterar cada valor numa coluna para maiúsculas/minúsculas

  1. Para abrir a consulta, na folha de cálculo, selecione uma célula nos dados e, em seguida, selecione Consulta > Editar. Para obter mais informações , consulte Criar, carregar ou editar uma consulta no Excel.

  2. Na Editor do Power Query, selecione Base > Editor Avançado, que é aberta com a instrução que criou na Fase 3: Promover a primeira linha a cabeçalhos.

  3. Na expressão let, converta cada valor de coluna ProductName em texto adequado com a função Table.TransformColumns, referindo-se ao passo anterior da fórmula de consulta "Primeira Linha como Cabeçalho", adicionando #"Maiúsculas Cada Word" à origem de dados e, em seguida, atribuindo #"Maiúsculas cada Word" ao resultado em.let    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],     #"First Row as Header" = Table.PromoteHeaders(Source),     #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})in     #"Capitalized Each Word"

  4. Para carregar a consulta para uma folha de cálculo, selecione Concluído e , em seguida , selecione Base> Fechar & Carregar > Fechar & Carregar.

Resultado:

Passo 4 - Resultado

Pode controlar o comportamento da barra de fórmulas no Editor do Power Query para todos os seus livros.

Apresentar ou ocultar a barra de fórmulas

  1. Selecione Opções e Definições do> de Ficheiros > Opções de Consulta.

  2. No painel esquerdo, em GLOBAL, selecione Editor do Power Query.

  3. No painel direito, em Esquema, selecione ou desmarque Apresentar a Barra de Fórmulas.

Ativar ou desativar o M Intellisense

  1. Selecione Opções de> de Ficheiros e Definições > Opções de Consulta .

  2. No painel esquerdo, em GLOBAL, selecione Editor do Power Query.

  3. No painel direito, em Fórmula, selecione ou desmarque Ativar M Intellisense na barra de fórmulas, editor avançado e caixa de diálogo de coluna personalizada.

Nota    A alteração desta definição entrará em vigor da próxima vez que abrir a janela de Editor do Power Query.

Consulte Também

Ajuda do Power Query para Excel

Criar e invocar uma função personalizada

Utilizar a lista Passos Aplicados (docs.com)

Utilizar funções personalizadas (docs.com)

Power Query fórmulas M (docs.com)

Lidar com erros (docs.com)

Precisa de mais ajuda?

Quer mais opções?

Explore os benefícios da subscrição, navegue em cursos de formação, saiba como proteger o seu dispositivo e muito mais.

As comunidades ajudam-no a colocar e a responder perguntas, a dar feedback e a ouvir especialistas com conhecimentos abrangentes.