Criar fórmulas do Power Query no Excel

O Editor do Power Query fornece uma experiência de consulta e modelagem de dados para o Excel que você pode usar para remodelar dados de muitas fontes de dados. Para exibir a janela Editor do Power Query, importe dados de fontes de dados externasem uma planilha do Excel, selecione uma célula nos dados e selecione Consultar > Editar. Veja a seguir um resumo dos componentes main.

1. A faixa de Editor do Power Query que você usa para moldar seus dados

2. O painel Consultas que você usa para localizar fontes de dados e tabelas

3. Menus de contexto que são atalhos convenientes para comandos na faixa de opções

4. A Visualização de Dados que exibe os resultados das etapas aplicadas aos dados

5. O painel Configurações de Consulta que lista propriedades e cada etapa na consulta

Nos bastidores, cada etapa em uma consulta é baseada em uma fórmula visível na barra de fórmulas.

Pode haver momentos em que você deseja modificar ou criar uma fórmula. As fórmulas usam o Power Query Formula Language, que você pode usar para criar expressões simples e complexas. Para obter mais informações sobre sintaxe, argumentos, observações, funções e exemplos, consulte Power Query linguagem de fórmula M.

Usando uma lista de campeonatos de futebol como exemplo, use Power Query para pegar dados brutos encontrados em um site e transformá-los em uma tabela bem formatada. Observe como as etapas de consulta e as fórmulas correspondentes são criadas para cada tarefa no painel Configurações de Consulta em Etapas Aplicadas e na barra de Fórmulas.

Procedimento

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

2. Na caixa de diálogo Navegador , selecione a tabela Resultados [Editar] à esquerda e selecione Transformar Dados na parte inferior. O editor Power Query é exibido.

3. Para alterar o nome da consulta padrão, no painel Configurações de Consulta , em Propriedades, exclua "Resultados [Editar]" e insira "Campeões da UEFA".

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

5. Para remover valores indesejados, selecione Coluna1, Selecione Home > Substituir Valores, insira "detalhes" na caixa Valores para Localizar e selecione OK.

6. Para remover linhas que têm a palavra "Ano" nelas, selecione a seta de filtro na Coluna1, desmarque a caixa marcar ao lado de "Ano" e selecione OK.

7. Para renomear os cabeçalhos de coluna, clique duas vezes em cada um deles e altere "Column1" para "Year", "Column4" para "Winner" e "Column5" para "Pontuação Final".

8. Para salvar a consulta, selecione Home > Fechar & Carregar.

Resultado

A tabela a seguir é um resumo de cada etapa aplicada e da fórmula correspondente.

Etapa e tarefa de consulta	Fórmula
Origem Conectar-se a uma fonte de dados da Web	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Navegação Selecione a tabela para se conectar	=Source{2}[Data]
Tipo Alterado Alterar os tipos de dados (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 em uma coluna selecionada	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Linhas Filtradas Filtrar valores em uma coluna	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Colunas Renomeadas Cabeçalhos de coluna alterados para serem significativos	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Importante    Tenha cuidado ao editar as etapas Origem, Navegação  e Tipo Alterado porque elas são criadas por Power Query para definir e configurar a fonte de dados.

Mostrar ou ocultar a barra de fórmulas

A barra de fórmulas é mostrada por padrão, mas se ela não estiver visível, você poderá redisplay-la.

• Selecione Exibir > Layout > Barra de Fórmulas.

Edit uma fórmula na barra de fórmulas

1. Para abrir uma consulta, localize uma carregada anteriormente no Editor do Power Query, 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 painel Configurações de Consulta, em Etapas Aplicadas, selecione a etapa que você deseja editar.

3. Na barra de fórmulas, localize e altere os valores de parâmetro e selecione o ícone Enter ou pressione Enter. Por exemplo, altere essa fórmula para também manter Column2:
   
   Before: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   After:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Selecione o ícone Enter ou pressione Enter para ver os novos resultados exibidos na Visualização de Dados.

5. Para ver o resultado em uma planilha do Excel, selecione Home > 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 caso adequado usando a função Text.Proper.

1. Para abrir uma consulta em branco, no Excel selecione Dados > Obter dados > de outras fontes > consulta em branco. Para obter mais informações , consulte Criar, carregar ou editar uma consulta no Excel.

2. Na barra de fórmulas, insira=Text.Proper("text value")e selecione o ícone Enter ou pressione Enter.
   
   Os resultados são exibidos na Visualização de Dados .

3. Para ver o resultado em uma planilha do Excel, selecione Home > Fechar & Carregar.

Resultado:

 Quando você cria uma fórmula, Power Query valida a sintaxe da fórmula. No entanto, ao inserir, reordenar ou excluir uma etapa intermediária em uma consulta, você pode potencialmente interromper uma consulta.  Verifique sempre os resultados na Visualização de Dados.

Importante    Tenha cuidado ao editar as etapas Origem, Navegação  e Tipo Alterado porque elas são criadas por Power Query para definir e configurar a fonte de dados.

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

Esse método faz uso de caixas de diálogo que variam dependendo da etapa. Você não precisa saber a sintaxe da fórmula.

1. Para abrir uma consulta, localize uma carregada anteriormente no Editor do Power Query, 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 painel Configurações de Consulta, em Etapas Aplicadas, selecione o ícone Editar Configurações da etapa que você deseja editar ou clicar com o botão direito do mouse na etapa e selecione Editar Configurações.

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

Inserir uma etapa

Depois de concluir uma etapa de consulta que remodela seus dados, uma etapa de consulta será adicionada abaixo da etapa de consulta atual. mas quando você insere uma etapa de consulta no meio das etapas, pode ocorrer um erro nas etapas subsequentes. Power Query exibe um aviso Inserir Etapa ao tentar inserir uma nova etapa e a nova etapa altera campos, como nomes de coluna, que são usados em qualquer uma das etapas que seguem a etapa inserida.

1. No painel Configurações de Consulta, em Etapas Aplicadas, selecione a etapa que você deseja preceder imediatamente a nova etapa e sua fórmula correspondente.

2. Selecione o ícone Adicionar Etapa à esquerda da barra de fórmulas. Como alternativa, clique com o botão direito do mouse em uma etapa e selecione Inserir Etapa Após. Uma nova fórmula é criada no formato :
   
   = <nameOfTheStepToReference>, como =Production.WorkOrder.

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

Reordenar uma etapa

• No painel Configurações de Consultas em Etapas Aplicadas, clique com o botão direito do mouse na etapa e selecione Mover para cima ou Mover para baixo.

Excluir etapa

• Selecione o ícone Excluir à esquerda da etapa ou clique com o botão direito do mouse na etapa e selecione Excluir ou Excluir Até Terminar. O ícone Excluir 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],   
   #x4#"First Row as Header" = Table.PromoteHeaders(Source)#x3
   

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.