Para criar fórmulas do Power Query no Excel, podemos usar a barra de fórmulas do Editor de Consultas ou o Editor Avançado. O Editor de Consultas é uma ferramenta incluída com o Power Query que permite criar consultas de dados e fórmulas. O idioma usado para criar essas fórmulas é a Linguagem M do Power Query. Há muitas fórmulas do Power Query para descobrir, combinar e refinar os dados.

Criar fórmulas do Power Query

Criar uma fórmula simples

Vamos ver agora um exemplo de uma fórmula simples. Pretendemos converter um valor de texto em que a primeira letra de cada palavra fique maiúscula. Para o efeito usamos a fórmula Text.Proper().

• No separador Dados > Obter e Transformar > escolhemos A partir de Outras Origens > Consulta em Branco.
• Na barra de fórmulas do Editor de Consultas, digitamos = Text.Proper("text value"). Neste caso, vamos substituir “text value” por “portal gestao”. Por fim, pressionamos Enter ou clicamos no ícone Enter, conforme figura abaixo.

• Esta apresentação é a que se visualiza no painel do Editor de Consultas, mas podemos ver o resultado na folha de cálculo. Basta «Fechar & Carregar».

O resultado na folha de cálculo é o seguinte:

Criar uma fórmula avançada

Para criarmos a fórmula avançada, vamos converter a primeira letra de cada palavra para maiúscula usando uma combinação de fórmulas. Podemos usar a Linguagem da Fórmulas do Power Query para combinar várias fórmulas em etapas de consulta, que tem um resultado de conjunto de dados. O resultado pode ser importado para uma folha de cálculo do Excel.

Vamos admitir, por exemplo, que tínhamos a seguinte tabela, em cujos nomes dos produtos queríamos converter a primeira letra, de cada palavra, para maiúscula. Assim, por exemplo, t-shirt deverá ficar T-Shirt.

Vamos ver, então, os passos a dar para realizar o proposto.

Quando criamos uma consulta avançada, devemos seguir este processo:

• Observe-se que a Linguagem da Fórmula do Power Query diferencia maiúsculas de minúsculas.
• Cada etapa de fórmula de consulta é constituída de acordo com uma etapa anterior, fazendo referência a uma etapa por nome.
• Produza uma etapa de fórmula de consulta usando a instrução “in”. Em geral, a última etapa de consulta é usada como “in”, sendo o resultado final do conjunto de dados.

Conforme começamos para criar uma fórmula simples, vamos igualmente ao separador Dados > Obter e Transformar > escolhemos A partir de Outras Origens > Consulta em Branco.

No Editor de Consultas, escolhemos Editor Avançado e abre-se a caixa da consulta (neste caso é a 2), com a sintaxe editada.

De acordo com o processo que vimos anteriormente, vamos usar a instrução “let” para iniciar a série.

Usamos a fórmula Excel.CurrentWorkbook() para o Power Query considerar a nossa tabela como fonte de dados.

Então, ficará:

Let
	Origem=Excel.CurrentWorkbook(){[Name=”Ordens”]}[Content]
In
	Origem

De referir que, por uma questão prática, devemos nomear antecipadamente a nossa tabela, evitando riscos de ligações erradas.

Depois de concluído este passo, veremos no painel:

Clicamos em «Fechar&Carregar» e vamos para a folha de cálculo.

Estamos agora preparados para converter a primeira letra de cada palavra da coluna Nome do Produto em letra maiúscula. Para esta ação, vamos usar a fórmula Table.TransformColumns().

Então, no Editor Avançado escrevemos:

let
	Source = Excel.CurrentWorkbook(){[Name="Ordens"]}[Content],
	#"Capitalized Each Word" = Table.TransformColumns(Source,{{"Nome do Produto", Text.Proper}})
in
	#"Capitalized Each Word"

De forma simples, = Table.TransformColumns(Source,{{"Nome do Produto", Text.Proper}})

O resultado final alterará a primeira letra de cada valor na coluna Nome do Produto para letra maiúscula e a nossa tabela deve ficar como a figura seguinte:

Vamos agora ver como inserir uma coluna, a partir de uma tabela de exemplo.

Queremos inserir, à direita desta coluna, uma outra com o título honorífico Dr. ou Dra.

Fazemos nova consulta em branco, e inserimos a  fórmula: 

=Excel.CurrentWorkbook(){[Name="Tabela4"]}[Content] e clicamos em «Fechar & Carregar».

De seguida, fazemos abrimos a consulta para criarmos a coluna condicional, no separador «Adicionar Colunas».

Abre-se a seguinte caixa, em que vamos inserir os parâmetros que desejamos.

Queremos que ao género masculino seja atribuído o título honorífico de dr. e ao género feminino o título honorífico de dra.

Assim,

Carregamos em OK e vemos o seguinte no painel:

Por fim, «Fechar & Carregar» para vermos o resultado final.

No Editor Avançado, podemos consultar a fórmula:

let
	Origem = Excel.CurrentWorkbook(){[Name="Tabela4"]}[Content],
	#"Tipo Alterado" = Table.TransformColumnTypes(Origem,{{"genero ", type text}}),
	#"Coluna Condicional Adicionada" = Table.AddColumn(#"Tipo Alterado", "título", each if [#"genero "] = "M" then "dr" else if [#"genero "] = "F" then "dra" else null )
in
	#"Coluna Condicional Adicionada"