Power query é uma ferramenta que faz parte da solução de Business Intelligence da Microsoft, o Power BI para o Office 365. Presente como complemento no Excel ‘on premises’, permite explorarmos a capacidade de descoberta de dados, além de efetuar rápidas e eficientes transformações. Veja a ilustração de sua interface inicial no menu do Excel na Figura 1.

Barra do menu Power Query

Figura 1. Barra do menu Power Query.

Para qualquer alteração feita nos dados dentro do Power Query, o Power Query Formula Language (conhecido como “M”) é acionado através de suas expressões e um trecho da Query é montado. Apesar das similaridades com as fórmulas do Excel, o “M” possui inúmeros comandos próprios que expandem os limites possíveis nos trabalhos para diversos cenários.

Veja neste artigo como funciona o Formula Language dentro da ferramenta Power Query. Além disso, abordaremos as estruturas principais que o compõem.

O Power Query Formula Language foi desenvolvido com foco nos analistas de dados, ou seja, usuários mais avançados que já estão mais confortáveis no uso da barra de fórmulas do Excel. Para esse tipo de usuário o intuito é garantir que o Formula Language seja familiar e fácil de lembrar, além de também ser similar ao DAX (Data Analysis Expressions), linguagem que utiliza uma coleção de funções e operadores para serem utilizados em fórmulas dentro do Power Pivot.

É importante deixar claro que muito pode ser feito no Power Query sem sequer tocar no Formula Language, ou seja, não é preciso ser um usuário avançado para desfrutar dos benefícios da ferramenta via interface.

Expressões e Valores

A estrutura central do “M” é a expressão, que pode e deve ser avaliada (computada) produzindo um ou mais valores. Embora vários valores possam ser escritos literalmente como uma expressão, eles não são.

Expressões são receitas para avaliação e os valores são o resultado da avaliação, como por exemplo, a expressão 1 + 1 é avaliada como o valor 2.

Tipos

Os exemplos abaixo ilustram os tipos diferentes de valores disponíveis no “M”. Como convenção, um valor é escrito usando a forma literal em que aparecem na expressão que avalia apenas esse valor. Note que o símbolo ‘//’ indica que o início de um comentário em seguida.

  • Primitivo – Valor único à parte, como um número, valor booleano, texto ou nulo. Veja exemplos na Listagem 1.

Listagem 1. Exemplos do tipo primitivo.


  123 //Um número
  True //Booleano
  “abc” //Um texto
  Null //Valor nulo
  • Lista – É uma sequência de valores ordenados. O “M” suporta infinitas listas, mas se escritas como literal, tem um tamanho fixo. Os colchetes delimitam o início e o fim da lista. Veja exemplos na Listagem 2.

Listagem 2. Exemplos do tipo lista.


  {123, true, “A”} // Lista contendo um número, valor booleano e um texto
  {1, 2, 3} // Lista com três números 
  • Registro – É um grupo de campos. Os campos são pares, onde o nome é um valor único em texto com o campo do registro. Veja um exemplo na Listagem 3.

Listagem 3. Exemplo do tipo registro.


  [A = 1, B = 2, C = 3] 

  • Tabela – É um grupo de valores organizados em colunas (que são identificados por nome) e linhas. Não existe sintaxe literal para criação da tabela, mas existe uma série de funções padrões que podem ser usadas para criar tabelas através das listas ou registros. Veja um exemplo na Listagem 4.

Listagem 4. Exemplo do tipo tabela.


  #table ( {“A”, “B”}, { {1, 2}, {3, 4} } ) 
  • Função – É um valor que, quando invocado com argumento, produz um novo valor. Funções são escritas usando os parâmetros nos parênteses. Veja um exemplo na Listagem 5.

Listagem 5. Exemplo do tipo função.


  (x, y) => (x + y) / 2

Referências adicionais complementando os exemplos apresentados acima podem ser encontrados na Tabela 1.

Valores Simples

Literal

Null

Null

Logical

True, false

Number

1, 1.2, 1.2e-3, #infinity, #nan

Text

“hello, world!”

Date

#date (2013, 3, 8)

Time

#time (15, 10, 0)

DateTime

#datetime (2013, 3, 8, 15, 10, 0)

DateTimeZone

#datetimezone (2013, 3, 8, 15, 10, 0, -8, 0)

Duration

#duration (1, 13, 59, 12, 34)

List

{ 1, 2, 3, “hello, world” }

Record

[ a = 1, b = { 1, 2, 3 }, C2 = true]

Table

#table (

{“n”, “n^2”}, {

{1, 2},

{2, 4}

})

Function

MyFunction = ( x, y, optional z ) =>

If z = null then

x + z

else

( x + y ) / z

Type

Type table [n = number, #”n^2” = number ]

Binary

#binary ({ 0x68, 0x65, 0x6C, 0x6C, 0x6F})

Tabela 1. Listagem com valores literais.

Linguagem

Para ter uma melhor visão da linguagem Power Query, vamos analisar a sintaxe em comparação com códigos em T-SQL e C#, assim vamos poder perceber visualmente as diferenças existentes em sua estrutura e escrita. Observe os exemplos presentes nas Listagens 6 a 8.

Listagem 6. Código T-SQL.


  SELECT Orders.OrderDate,  Products.OrderID,  Products.ProductsSKU
  FROM Products
  INNER JOIN Orders ON Products.OrderID = Orders.ID
  ORDER BY Products.ProductsSKU

Listagem 7. Código C#.


  Products.Join  { Orders, Products,
  o  =>  o.OrderID ,  p  =>  p.OrderID,
  ( p ,  o)  =>  new  {  o.OrderDate ,  p.OrderID ,  p.ProductSKU  }
  }.OrderBy  {  p  =>  p.ProductSKU  }

Listagem 8. Código Power Query Fórmula Language.


  Let
  Joined = Table.Join  (Product, “OrderID”, Orders, “OrderID”),
  Columns = Table.SelectColumns  (Joined, { “OrderDate” ,  “OrderID” ,  “ProductSKU” } ),
  Sorted = Table.Sort  ( Columns ,  “ProductSKU” ),
  In
  Sorted

Grupos

As expressões presentes no “M” estão divididas em três grupos:

  1. Acesso aos dados – Todas as fontes de dados possíveis de conexão. O código para conexão a fonte de dados a partir de arquivos do Excel é exibido nas Listagens 9 e 10.
    Listagem 9. Modelo de conexão a arquivos do Excel.
    Excel.Workbook ( workbook as binary, optional useHeaders as nullable logical ) as table

    Listagem 10. Exemplo de conexão a arquivos do Excel.
    Excel.Workbook ( File.Contents ( " localExcelFile.xlsx " ) )

    Nas Listagens 11 e 12 temos o código para conexão a fontes de dados ODataFeed.
    Listagem 11. Modelo de conexão a fonte de dados ODataFeed.
    OData.Feed (serviceUri as text, optional headers as nullable record, optional useConcurrentRequests as nullable logical ) as table

    Listagem 12. Exemplo de conexão a fonte de dados ODataFeed.
    OData.Feed ( " http://services.odata.org/Northwind/Northwind.svc " )

    Já nas Listagens 13 e 14 temos o código para conexão ao banco de dados SQL Server.
    Listagem 13. Modelo de conexão a fonte de dados SQL Server.
    Sql.Databases ( server as text ) as table

    Listagem 14. Exemplo de conexão a fonte de dados SQL Server.
    Sql.Database ( " localhost " , " Northwind " )
  2. Transformações – A linguagem oferece um grande grupo de opções de transformações, como conversões, filtros, junções e etc.
    Na Listagem 15 podemos conferir o modelo de código para retornar o ano de um campo do tipo DateTime. Já na Listagem 16 temos o exemplo pronto da captura do ano a partir do campo DateTime.
    Listagem 15. Modelo de captura do ano a partir de um campo DateTime.
    Date.Year ( dateTime as datatime ) as nullable number 

    Listagem 16. Exemplo de captura do ano a partir de um campo DateTime.
    Date.Year ( DateTime.FromText ( " 2011-02-19 " ) ) equals 2011

    Já nas Listagens 17 e 18 conferimos o modelo e o exemplo, respectivamente, do código para retornar uma lista ordenada por um critério de comparação.
    Listagem 17. Modelo para retornar uma lista ordenada.
    List.Sort ( list as list, optional comparisonCriteria as any ) as list

    Listagem 18. Exemplo do retorno de uma lista ordenada.
    List.Sort ( { 2, 1 }, Order.Descending ) equals { 2, 1 }

    Já nas Listagens 19 e 20 temos o código para remover uma ou mais colunas de uma tabela.
    Listagem 19. Modelo para remover coluna da tabela.
    Table.RemoveColumns ( table as table, columns as any, aptional missingField as nullable number ) as table

    Listagem 20. Exemplo da remoção da coluna da tabela.
    Table.RemoveColumns ( Table.FromRecords ( { [ A = 1, B = 2, C = 3 ] } ), "A" )
  3. Outros – A linguagem também oferece uma gama de funções binárias.Confira nas Listagens 21 e 22 o modelo e o exemplo, respectivamente, de uma função.
    Listagem 21. Modelo para retornar valores binários.
    Binary.From ( Value as any, optional encoding as nullable number ) as nullable binary

    Listagem 22. Exemplo do retorno de valores binários
    Binary.From ( "1011 " ) equals Binary.FromText ( " 1011 " , BinaryEncoding.Base64 )

Validação

O modelo de validação da linguagem “M” é baseado na mesma validação que geralmente é encontrada nas planilhas, onde a ordem do cálculo pode ser determinada baseada nas dependências entre as fórmulas da célula.

Se você escreve fórmulas nas planilhas do Excel, provavelmente irá reconhecer que as fórmulas presentes no exemplo da esquerda resultarão nos valores da direita após serem calculados. Veja Figura 2.

Fórmula calculada na
planilha do Excel

Figura 2. Fórmula calculada na planilha do Excel.

No “M”, partes de uma expressão podem referenciar outras partes de expressões por nome e o processo de validação irá automaticamente determinar a ordem na qual as expressões referenciadas são calculadas.

Podemos usar um registro para produzir uma expressão que é equivalente ao exemplo da Figura 2. Quando inicializado o valor de um campo, podemos referenciá-lo a outros campos com registro apenas usando o nome do campo. Veja um exemplo na Listagem 23.

Listagem 23. Exemplo de referenciamento.


  [
  A1 = A2 * 2,
  A2 = A3 + 1,
  A3 = 1
  ]

A expressão acima é equivalente a Listagem 24.

Listagem 24. Exemplo de expressão equivalente.


  [
  A1 = 4,
  A2 = 2,
  A3 = 1
  ]

Funções

No “M” uma função é um mapeamento a partir de um conjunto de valores de entrada para um único valor de saída. A função é escrita pelo primeiro nome que é requerido no conjunto de valores de entrada (os parâmetros para a função) e em seguida fornecendo uma expressão que irá computar os da função usando aqueles valores de entrada (o corpo da função) seguidos pelo sentido mostrado no símbolo “=>”. Observe o exemplo da Listagem 25.

Listagem 25. Exemplo de função.


  (x) => x + 1      //função que adiciona um valor
  (x , y) => x + y //função que adiciona dois valores

Uma função é um valor como um número ou um valor em texto. O exemplo da Listagem 26 mostra uma função em que o valor é um campo ‘Add’ que posteriormente é chamado ou executado através de vários outros campos.

Listagem 26. Exemplo de função.


  [ 
  Add = (x, y) => x + y, 
  OnePlusOne = Add(1, 1), // 2 
  OnePlusTwo = Add(1, 2) // 3 
  ]

Biblioteca

O “M” inclui um conjunto comum de definições disponíveis para uso através de expressões chamadas "Biblioteca Padrão". Essas definições consistem em um grupo de valores nomeados que são fornecidos pela biblioteca e disponíveis para o uso dentro de uma expressão sem precisar ser definido explicitamente na expressão. Veja um exemplo na Listagem 27.

Listagem 27. Exemplo Valores nomeados da biblioteca.


  Number.E // Euler’s number e (2.7182…) 
  Text.PositionOf("Hello", "ll") // 2

Operadores

O “M” inclui um grupo de operadores que podem ser usados em expressões. Os operadores são aplicados a operandos para formar expressões simbólicas. Por exemplo, na expressão 1 + 2 os números 1 e 2 são os operandos e o operador é o símbolo ‘+’.

O significado de um operador pode variar dependendo do tipo de valores que os operandos são. Por exemplo, o operador da soma ‘+’ pode ser utilizado para outros tipos de valores diferentes de números, como mostra o exemplo da Listagem 28.

Listagem 28. Exemplo dos diferentes operandos.


  1 + 2 // numeric addition: 3 
  #time(12,23,0) + #duration(0,0,2,0) // time arithmetic: #time(12,25,0)

Metadados

Metadados é a informação sobre o valor que está associado ao valor. Metadado é representado como um valor de registro, chamado ‘Registro de Metadado’. Os campos do registro de metadado podem ser usados para armazenar metadados para um valor.

Todo valor tem um registro de metadado e se esse valor não for especificado, então o registro de metadado estará vazio (não tem campos).

Os registros de metadado fornecem uma maneira de associar informações adicionais com qualquer tipo de registro de uma maneira discreta.

Um valor de registro de metadado ‘y’ é associado a um valor existente ‘x’ usando a sintaxe ‘x meta y’. Por exemplo, o seguinte registro de metadado associado com os campos ‘Rating’ e ‘Tags’ com o valor de texto ‘Mozard’. Veja como fica o exemplo na Listagem 29.

Listagem 29. Exemplo de metadados.


  "Mozart" meta [ Rating = 5, Tags = {"Classical"} ]
  

Expressão ‘Let’

Muitos dos exemplos mostrados até agora não chegaram nem perto de mostrar todos os valores literais de expressões no resultado de uma expressão. A expressão ‘Let’ permite que um grupo de valores seja computado, associado a nomes e usado em expressões subsequentes. Por exemplo, veja Listagem 30.

Listagem 30. Exemplo expressão Let.


  let 
  Sales2007 = 
  [ 
  Year = 2007, 
  FirstHalf = 1000, 
  SecondHalf = 1100, 
  Total = FirstHalf + SecondHalf // 2100 
  ], 
  Sales2008 = 
  [ 
  Year = 2008, 
  FirstHalf = 1200, 
  SecondHalf = 1300, 
  Total = FirstHalf + SecondHalf // 2500 
  ] 
  in Sales2007[Total] + Sales2008[Total] // 4600

O resultado da expressão acima é o valor numérico 4600, que foi computado a partir dos valores associados aos nomes ‘Sales2007’ e ‘Sales2008’.

Expressão ‘If’

A expressão If seleciona entre duas expressões baseada numa condição lógica. Por exemplo, veja a Listagem 31.

Listagem 31. Exemplo expressão If.


  if 2 > 1 then 
  2 + 2 
  else 
  1 + 1  

A primeira expressão (2 + 2) é selecionada se a expressão lógica (2 > 1) for verdadeira e a segunda expressão (1 + 1) é selecionada se for falsa. A expressão selecionada (nesse caso 2 + 2) é validada e se torna o resultado da expressão ‘If’ (4).

Erros

Um erro é a indicação que o processo de validação de uma expressão não pode produzir um valor.

Erros são apresentados pelos operadores e funções que se deparam com uma condição de erro ou então usando uma expressão de erro. Erros são tratados usando a expressão ‘try’ e quando são apresentados, um valor é especificado, que pode ser usado para indicar por que o erro ocorreu. Por exemplo, veja a Listagem 32.

Listagem 32. Exemplo do tratamento de erro


  try error "negative unit count" otherwise 42

Estrutura dos documentos

No “M” o documento é uma sequência ordenada por caracteres Unicode. O “M” permite diferentes classes de caracteres Unicode em diferentes partes de um documento, que por sua vez, consiste exatamente de uma expressão ou de um grupo de definições organizadas em seções.

Seções são um conceito organizacional que permite expressões similares serem nomeadas e agrupadas em um documento.

Demonstrações na Aplicação

Uma vez que temos os dados que queremos transformar dentro do editor de consultas do Power Query, podemos observar que a cada alteração feita, um novo passo é adicionado no log de etapas aplicadas e por trás desses passos existe uma fórmula, que é uma função para determinada ação apresentada na barra superior. Veja um exemplo na Figura 3.

Função sendo utilizada na barra de fórmulas
no editor de consultas do Power Query

Figura 3. Função sendo utilizada na barra de fórmulas no editor de consultas do Power Query.

No botão do editor avançado podemos ver o script inteiro da Query que foi montada utilizando as expressões do “M”. Reparem que cada linha da Query corresponde a uma função e para as duas primeiras temos as informações de conexão com a fonte de dados e nas demais são apresentadas as transformações efetuadas, como a junção de colunas e ordenação. Veja a Figura 4.

Editor avançado com a query criada a
partir das transformações efetuadas

Figura 4. Editor avançado com a query criada a partir das transformações efetuadas.

Através da própria interface é possível obter mais informações a respeito de determinada função na biblioteca de ajuda. Dessa maneira obtemos a documentação do comando e sua utilização. Para isso, a função deve ser escrita na barra de fórmula sem a passagem de parâmetros, como mostra a Figura 5.

Biblioteca de ajuda para as funções

Figura 5. Biblioteca de ajuda para as funções.

Também podemos invocar a execução da função a qualquer momento utilizando o botão de chamar, presente na interface quando colocamos a expressão desejada na barra de fórmulas, assim como foi feito ao utilizar a biblioteca de ajuda. Ao invocar a função, uma nova etapa será criada com o resultado, mas é necessário satisfazer os pré-requisitos apontados para cada tipo de função. Veja a Figura 6.

Chamada da função para execução

Figura 6. Chamada da função para execução.

Grupos de Tipos no Sistema

  • Tipos pequenos: Null, Logical, Number, Text, Binary, Time, Datetime, Datetimezone, Duration.
  • Tipos complexos: List, Record, Table.

Vamos demonstrar o uso dos tipos complexos, já explicados anteriormente. Para os exemplos utilizaremos uma query a partir do zero. Para isso, deve ser selecionado uma consulta nula na aba de outras fontes para conexão, como mostra a Figura 7.

Consulta Nula a partir do menu de
outras fontes de conexão

Figura 7. Consulta Nula a partir do menu de outras fontes de conexão.

  • List (Listas): Utilizando esse tipo de expressão é possível retornar listas e converter em tabela para que possa ser utilizada, como mostra a Figura 8.

Tipo de função List

Figura 8. Tipo de função List.

  • Record (Registros): Para esse tipo de expressão de registro normalmente é retornado o nome e o valor associado a ele, como mostra a Figura 9.

Tipo de função Record

Figura 9. Tipo de função Record.

Podemos também utilizar uma lista em combinação com os registros, como mostra a Figura 10.

Tipo de função Record em conjunto
com o tipo List

Figura 10. Tipo de função Record em conjunto com o tipo List.

Reparem na Figura 11 a apresentação dessas informações após carregarmos em tabela.

Resultado da junção do tipo Record e
List

Figura 11. Resultado da junção do tipo Record e List.

  • Table (Tabelas): Utilizando a expressão de tabela os registros são retornados de forma tabular, como mostra a Figura 12.

Tipo de função Table

Figura 12. Tipo de função Table.

Reparem que um erro foi apresentado e o detalhamento está descrito no rodapé. Neste caso é devido a expressão necessitar que todos os parâmetros estejam no mesmo formato. Sendo assim, teremos que modificar conforme mostra a Figura 13.

Tratamento do erro na função

Figura 13. Tratamento do erro na função.

Performance

O Power Query utiliza de alguns recursos para garantir uma boa performance em diversas operações com os dados, como o trabalho em memória. Uma das capacidades que também auxilia neste quesito de performance é o chamado Query Folding. Ele permite que o Power Query empurre, sempre que possível, a execução das Queries para a fonte, ou seja, processe todas as transformações na origem e apenas carregue o retorno no Power Query em seguida.

Neste exemplo da Figura 14 podemos ver que o Power Query gera, a partir do “M”, uma nova query em T-SQL para que seja executada na origem.

Query do “M” sendo traduzida em T-SQL para execução

Figura 14. Query do “M” sendo traduzida em T-SQL para execução.

Nem tudo pode ser empurrado da fonte de origem para execução, pois depende das capacidades da fonte de origem e algumas restrições, como controle de privacidade e o uso do Native Database Query.

OBS: Para quem está acostumado a usufruir do Intellisense, ainda não está presente para o desenvolvimento com o “M”, mas a previsão é que esse recurso esteja disponível em breve.

Com esse artigo percebemos que o Power Query Formula Language é uma linguagem muito eficiente e prática. Com certeza, com a expansão do Power Query, o seu uso ficará inevitável para obter resultados além das capacidades normais da ferramenta.