Criar fórmulas do Power Query no Excel

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.

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.

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.

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

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 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 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 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:

 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 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 à 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.")

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 à 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 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:

Depois:

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.

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:

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:

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:

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.