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 main.

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 marcar 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.

Resultado

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 exibir apenas as colunas de interesse	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Valor Substituído Substituir valores para limpo 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 Renomeadas 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 "Mr.", 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.

Excluir etapa

• 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 em uma coluna em um caso adequado usando uma combinação de fórmulas no Editor Avançado.

Por exemplo, você tem uma tabela do Excel, chamada Orders, com uma coluna ProductName que deseja converter em caso apropriado.

Antes:

Depois:

Ao criar uma consulta avançada, você cria uma série de etapas de fórmula de consulta com base na expressão let . Use a expressão let para atribuir nomes e calcular valores que são referenciados pela cláusula in , que define a Etapa. Este exemplo retorna o mesmo resultado que o da seção "Criar uma fórmula na barra de fórmulas".

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

Você verá que cada etapa é criada em uma etapa anterior, referindo-se a uma etapa por nome. Como lembrete, o Power Query Formula Language é sensível a casos.

Fase 1: Abra o Editor Avançado

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

2. No Editor do Power Query, selecione Home > Editor Avançado, que abre com um modelo da expressão let.

Fase 2: Definir a fonte de dados

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

2. Para carregar a consulta em uma planilha, selecione Concluído e selecione Home > Fechar & Carregar > Fechar & Carregar.

Resultado:

Fase 3: Promover a primeira linha para cabeçalhos

1. Para abrir a consulta, na planilha selecione uma célula nos dados e selecione Consultar > Editar. Para obter mais informações, consulte Criar, carregar ou editar uma consulta no Excel (Power Query).

2. No Editor do Power Query, selecione Home > Editor Avançado, que abre com a instrução criada na Fase 2: Definir a fonte de dados.

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

4. Para carregar a consulta em uma planilha, selecione Concluído e selecione Home > Fechar & Carregar > Fechar & Carregar.

Resultado:

Fase 4: alterar cada valor em uma coluna para o caso apropriado

1. Para abrir a consulta, na planilha selecione uma célula nos dados e selecione Consultar > Editar. Para obter mais informações , consulte Criar, carregar ou editar uma consulta no Excel.

2. No Editor do Power Query, selecione Home > Editor Avançado, que abre com a instrução criada na Fase 3: Promover a primeira linha para cabeçalhos.

3. Na expressão let, converta cada valor de coluna ProductName em texto adequado usando a função Table.TransformColumns, referindo-se à etapa anterior da fórmula de consulta "Primeira Linha como Cabeçalho", adicionando #"Capitalizado Cada Word" à fonte de dados e atribuindo #"Capitalizado Cada Word" ao resultado.
   
   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 em uma planilha, selecione Concluído e selecione Home > Fechar & Carregar > Fechar & Carregar.

Resultado:

Você pode controlar o comportamento da barra de fórmulas no Editor do Power Query para todas as pastas de trabalho.

Exibir ou ocultar a barra de fórmulas

1. Selecione Opções de> de Arquivo e Configurações > Opções de Consulta.

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

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

Ativar ou desativar o M Intellisense

1. Selecione Opções de> de Arquivo e Configuraçõ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 Habilitar M Intelisense na barra de fórmulas, editor avançado e caixa de diálogo de coluna personalizada.

Observação    A alteração dessa configuração entrará em vigor na próxima vez que você abrir a janela Editor do Power Query.