Introdução ao Power Query M Code

Power Query é uma ferramenta muito poderosa de extração e transformação de dados que vem cozida no Excel 2016 (ou posterior), Excel para Office 365 e Power BI.

Pode ser encontrada na guia Dados na seção Obter & Transformar Dados da fita.

É muito poderosa e também muito fácil de usar e o editor de consulta tem uma interface de usuário super intuitiva para um usuário do Excel. Muitos passos de transformação podem ser facilmente executados a partir do editor de consultas de energia do ribbon e você não precisa saber nenhum código para limpar e moldar seus dados.

Nos bastidores do editor amigável, o Excel está traduzindo cada passo do seu processo de transformação desses comandos do ribbon para a linguagem de código M da consulta de energia.

Este post irá apresentar-lhe os conceitos básicos da linguagem de código M da consulta de poder e irá assumir que você já conhece os conceitos básicos da consulta de poder.

O que é código M?

O M significa Mash-up de dados, pois a consulta de poder é tudo sobre a conexão a várias fontes de dados diferentes e “Mashing” para cima.

O código M é a linguagem por trás dos bastidores da consulta de poder. Quando você cria uma transformação de dados na interface do editor de power query, o Excel está escrevendo o código M correspondente para a consulta.

M é uma linguagem funcional, o que significa que ela é escrita principalmente com funções que são chamadas para avaliar e retornar resultados. O código M vem com uma biblioteca muito grande de funções pré-definidas disponíveis e você também pode criar a sua própria.

Where Can You Write Power Query M Code?

Se você quiser começar a escrever ou editar código M, você vai precisar saber onde você pode fazer isso. Há dois lugares onde é possível, na barra de fórmulas ou no editor avançado.

A Barra de Fórmulas

Para cada passo que é criado na interface do editor, você pode ver o código M correspondente na barra de fórmulas.

Se você não vê a barra de fórmula, vá para a aba View e verifique se a opção Formula Bar está marcada.

Você pode editar o código M para qualquer passo de uma consulta clicando na fórmula e editando o código existente. Quando terminar, você pode aceitar qualquer alteração clicando na marca de verificação ou pressionando Enter. Você também pode descartar suas alterações clicando no X ou pressionando Esc.

Você também pode criar passos inteiramente novos em sua consulta com a barra de fórmula clicando no símbolo fx ao lado da barra de fórmula. Isto irá criar um novo passo que referencia o passo anterior pelo nome e então você pode criar qualquer código M que você precisar.

O editor avançado

A barra de fórmula só mostra o código M para o passo atualmente selecionado na consulta, mas o editor avançado é onde você pode ver e editar o código M para toda a consulta.

Você pode abrir o editor avançado a partir de dois lugares na fita do editor. A partir da aba Home ou da aba View pressione o botão Advanced Editor.

Apesar do moniker “avançado”, o editor é o editor de código mais básico que você verá e não (ainda) contém nenhum auto completo intellisense, destaque de sintaxe ou recursos de formatação automática.

O editor avançado exibirá o nome da consulta, mostrará o código M para a consulta e exibirá um aviso sobre qualquer violação de sintaxe no código M. É isso!

Biblioteca de funções padrão

Desde que o código M é uma linguagem funcional, é tudo sobre as funções e o código M vem com uma grande biblioteca de funções predefinidas chamada biblioteca padrão.

Informações sobre todas as funções disponíveis da biblioteca padrão podem ser encontradas na página da Web de referência da Microsoft Power Query M, incluindo a sintaxe da função e exemplos.

A biblioteca padrão também pode ser explorada a partir do editor de consulta de energia usando a palavra-chave #shared.

Quando inserida na barra de fórmulas, você pode então explorar todas as funções disponíveis clicando na palavra Função à direita do nome da função. Você encontrará a mesma sintaxe e exemplos da página de referência.

Sensibilidade a casos

Uma das primeiras coisas que alguém precisa estar ciente ao escrever código M é que é uma linguagem sensível a casos.

Isto significa que x não é a mesma coisa que X ou “abc” não é a mesma coisa que “ABC”. Isto é verdade para quaisquer valores, variáveis, funções, etc.

Expressões e Valores em Power Query

Power Query é tudo sobre Expressões e Valores.

Uma expressão é algo que pode ser avaliado para retornar um valor em Power Query. 1 + 1 é uma expressão que avalia para o valor 2.

Um valor é um único dado. Valores podem ser valores únicos como números, texto, lógico, nulo, binário, data, hora, datetime, datetimezone ou durações.

Valores também podem ter estruturas mais complexas do que valores únicos como listas, registros e tabelas. Você também pode ter valores que são uma combinação de listas, registros e tabelas. Listas de listas, tabelas de listas, tabelas de tabelas, etc… são todas estruturas de valores possíveis.

Valores literais únicos

Valores literais únicos são o bloco básico de construção de todos os outros valores.

• 123,45 é um valor numérico.
• “Hello World!” é um valor de texto.
• true é um valor lógico.
• null representa a ausência de um valor.

Valores Intrínsecos Únicos

Valores intrínsecos são construídos usando as várias funções intrínsecas.

• #time(horas, minutos, segundos)
• #date(anos, meses, dias)
• #datetime(anos, meses, dias, horas, minutos, segundos)
• #datetimezone( anos, meses, dias, horas, minutos, segundos, offset-hours, offset-minutes)
• #duração(dias, horas, minutos, segundos)

Por exemplo, para construir a data 2018-12-31 você precisaria construir usando a função intrínseca #date(2018, 12, 31).

Valores estruturados

Listas

Uma lista é uma sequência ordenada de valores.

Você pode definir uma lista usando chaves encaracoladas. {1, 2, 3} é uma lista contendo os números 1, 2 e 3. Como a ordem é importante, esta não é a mesma lista que {3, 2, 1}.

{“Olá”, “Mundo”} é uma lista contendo o texto “Olá” e “Mundo”.

Listas de listas também são possíveis, portanto {{{1, 2}, {3, 4, 5}}} é uma lista de duas listas. A primeira lista contém os números 1 e 2 e a segunda lista contém os números 3, 4 e 5.

Pode criar listas sequenciais usando o formato {x…y}. {2..5} irá produzir a lista {2, 3, 4, 5}. Isto também funciona para caracteres de texto. {“a”… “d”} irá produzir a lista {“a”, “b”, “c”, “d”}.

Você também pode ter uma lista sem itens, {} é a lista vazia.

Desde que as listas são ordenadas, podemos referenciar itens na lista com um número de índice baseado em zero. {1, 2, 3}{2} irá avaliar a 3 uma vez que este é o 2º item da lista (baseado em um índice zero).

Registros

Um Registro é uma seqüência ordenada de Campos. Cada campo consiste em um nome de campo que identifica exclusivamente o campo e um valor de campo que pode ser de qualquer tipo de valor.

É possível definir um registro usando chaves quadradas. é um registo com dois campos. O primeiro campo no registo tem um nome de campo de FirstName e valor de “John”. O segundo campo no registo tem um nome de campo de Age e um valor de 38.

Records of records are also possible, ] é um registo com um campo com um nome de campo de Person e um valor de campo que é um record.

Empty records are also possible, é o registo vazio.

Pode referenciar o valor do campo num registo pelo nome do campo. irá avaliar para “John”.

Tabelas

Uma Tabela é uma sequência ordenada de Linhas onde cada linha é uma lista.

As tabelas só podem ser construídas usando uma função intrínseca. Você pode construir uma tabela usando a função #table() a partir de uma lista de títulos de coluna e uma lista de linhas.

#table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}) irá criar uma tabela com 2 colunas, 3 linhas e os títulos de coluna de Letras e Números.

É possível criar uma tabela vazia usando listas vazias na função #table() intrínseca. A #table({}, {}) irá produzir uma tabela vazia.

Pode referenciar qualquer valor numa tabela com o índice de linhas baseado em zero e o nome do cabeçalho da coluna. #table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}){2} irá avaliar para “C” já que esta é a 2ª linha (baseada em um índice zero) da coluna Letras.

Expressões

Expressões são qualquer coisa que pode ser avaliada para um valor. Isto é verdade para os próprios valores. Por exemplo, a expressão 1 avalia para o valor 1.

Embora você normalmente pensaria em expressões como sendo feitas de operações ou funções mais complexas.

Por exemplo:

• A expressão 1 + 1 avalia para 2.
• A expressão 3 > 2 avalia para true.
• A expressão “Hello ” &”World” avalia para “Hello World”.
• A expressão Text.Upper(“Hello World”) avalia para “HELLO WORLD”.

Operadores

Ainda à biblioteca padrão, o código M também tem uma lista de funções especiais chamadas operadores. Estes tomam exatamente dois valores (ou expressões que avaliam para um valor) e retornam um único valor.

Aritmética

O código M vem com os operadores aritméticos básicos que você esperaria e estão acostumados com o Excel normal +, -, * e /. Estes permitirão que você adicione, subtraia, multiplique e divida valores respectivamente.

Estes podem ser usados com vários outros tipos de valores que não apenas números. Por exemplo, você pode adicionar uma duração a uma data.

#date(2018,12,25) + #duration(7, 0, 0, 0) irá avaliar para 2019-01-01.

Comparação

Você pode comparar valores em código M usando os operadores de comparação <, >, <=, >=, =, <>.

• x < y irá avaliar se x é menor que y.
• x > y irá avaliar se x é maior que y.
• x <= y avaliará como verdadeiro se x for menor ou igual a y.
• x >= y avaliará como verdadeiro se x for maior ou igual a y.
• x = y será avaliado como verdadeiro se x for igual a y.
• x <> y será avaliado como verdadeiro se x não for igual a y.

Estes podem ser usados com vários tipos de valores. Por exemplo, você pode comparar duas listas com o operador igual.

{1,2,3,4} = {1,2,3} irá avaliar a falso já que as listas não são iguais.

Concatenação e Fusão

Você pode concatenar textos e fundir listas, registros e tabelas usando os amplificadores e & operador.

Por exemplo:

"Hello " & "World" irá avaliar a “Hello World”.

{1,2,3} & {3,4,5} irá avaliar para {1,2,3,3,4,5}.

Lógico

Você pode realizar operações sobre valores booleanos (ou expressões que avaliam para valores booleanos) com os não, e ou operadores.

Código de Comentários

Como você esperaria de qualquer linguagem de programação, é possível adicionar comentários ao seu código.

Existem dois tipos de comentários possíveis no código M. Comentários de uma linha e comentários de várias linhas.

Comentários de uma linha

M code goes hereM code goes here //This is a single line commentM code goes here

Um comentário de uma linha pode ser criado precedendo o comentário com dois caracteres de barra //. Qualquer coisa na mesma linha antes disto será interpretado como código M, qualquer coisa depois disto será interpretado como um comentário.

Comentários de múltiplas linhas

M code goes here /*This is a commenton multiple lines*/ M code goes here

Um comentário de múltiplas linhas pode ser criado colocando o comentário entre /* e */ caracteres. Qualquer coisa fora destes será interpretada como código M. Qualquer coisa entre estes será interpretada como um comentário.

Let Statement

O let statement permite que um conjunto de valores seja avaliado e atribuído a nomes de variáveis e depois utilizado numa expressão subsequente que segue o in statement.

let a = 1, b = 2, c = a + bin c

Esta expressão é composta por três expressões que são avaliadas após o let statement. Cada expressão é separada por uma vírgula, exceto a última antes da expressão no comando. Neste exemplo o let e na expressão inteira será avaliado até 3.

let c = a + b, b = 2, a = 1in c

Você pode estar pensando que as expressões dentro de um let precisam aparecer na ordem em que precisam ser avaliadas. Este não é o caso! O código acima é perfeitamente válido e também será avaliado para 3. O avaliador de código M calculará automaticamente a ordem dos cálculos necessários com base nas dependências de expressão.

É obviamente mais fácil para uma pessoa ler o código M se ele estiver escrito na ordem de avaliação, mas também há outra vantagem. Expressões aparecerão como passos separados na janela de Passos Aplicados. Quando escritas fora de ordem, as expressões aparecerão como um passo combinado.

let a = 1, b = 2in a + b

Você também pode avaliar expressões dentro de uma parte de um let… em… expressão.

Nomes de Variáveis

let #"Is this a variable name? Wow!!!" = 1 + 1in #"Is this a variable name? Wow!!!"

Você pode atribuir praticamente qualquer nome às suas expressões usando os caracteres #””. Você pode até usar os caracteres de espaço e outros caracteres especiais. Usando palavras-chave reservadas é a única exceção.

Os nomes das variáveis são o que aparecerá nas Etapas Aplicadas do editor de consultas, portanto, poder usar caracteres de espaço é uma ótima característica.

Cada Declaração

A cada expressão é um abreviação para declarar funções com um único parâmetro chamado _ (underscore).

let Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}), #"Added Custom" = Table.AddColumn(Source, "Double", each 2*)in #"Added Custom"

Neste exemplo estamos criando uma nova coluna que multiplica a coluna Números por 2 para cada linha.

let Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}), #"Added Custom" = Table.AddColumn(Source, "Double", (_) => 2*_)in #"Added Custom"

Podemos criar a mesma consulta usando a sintaxe underscore que é semanticamente equivalente a cada declaração. Ambas as consultas funcionarão da mesma forma.

Se Então as Expressões Mais Usadas

O código M é bastante esparso em comparação com outras linguagens quando se trata de expressões lógicas. Não há instruções de caso ou loop disponíveis. Só há uma expressão if… então… outra expressão… disponível.

if then else 

A sintaxe é direta e é como a maioria das outras linguagens de programação. Ela pode aparecer toda em uma linha, ou pode ser apresentada em linhas separadas para facilitar a leitura.

Tente Declarações de Caso contrário

Errores podem acontecer ao tentar realizar operações que requerem tipos particulares de dados. Por exemplo, tentar multiplicar um número com um valor de texto resultará num erro.

let Source = #table({"Number", "Number and Text"}, {{2, 2}, {2, "Hello"}}), #"Added Custom" = Table.AddColumn(Source, "Product", each try * otherwise 0)in #"Added Custom"

Erros podem ser evitados usando a expressão try… caso contrário….

Isto irá evitar erros nos resultados da sua consulta e permitir-lhe-á substituir erros por qualquer valor ou expressão.

Funções

Uma função é um mapeamento a partir de um conjunto de valores de parâmetro para um valor. Juntamente com as funções da biblioteca padrão, o código M permite que você crie suas próprias funções.

let Product = (x,y) => x * y, Result = Product(2,3)in Result

Esta consulta define uma função que multiplica dois números. Depois a consulta chama e avalia a função com os valores 2 e 3 que avalia para 6.

Funções com parâmetros opcionais

Existem dois tipos de parâmetros de função, um parâmetro obrigatório e um opcional.

Parâmetros obrigatórios devem ser sempre especificados quando uma função é invocada.

Parâmetros opcionais não precisam de ser especificados quando uma função é invocada. Se o parâmetro opcional estiver faltando, então o valor passado para a função será nulo.

let Product = (x, optional y) => if y is null then x else x * y, Result = Product(2)in Result

Esta função tem um segundo argumento opcional. Então a consulta chama e avalia a função usando apenas o parâmetro requerido com um valor 2, que avalia para 2. Note que a função precisa ser considerada como y sendo nula, caso contrário um argumento opcional ausente poderia resultar em uma função avaliando para um erro.

Funções recursivas

Também é possível escrever uma função que se refere a si mesma usando o operador @ scoping.

let Fibonacci = (n) => if n = 1 then 1 else if n = 2 then 1 else @Fibonacci(n-1) + @Fibonacci(n-2), Result = Fibonacci(7)in Result

A sequência Fibonacci é um exemplo de uma função que é definida recursivamente. O próximo número na sequência é definido como a soma dos dois números anteriores. Então para obter o enésimo número, você precisa saber os números (n-1)th e (n-2)th.

Esta função vai encontrar o enésimo número de Fibonacci adicionando os números (n-1)th e (n-2)th de Fibonacci.

A consulta avalia até 13, já que 13 é o sétimo número de Fibonacci.

Aqui estão alguns exemplos mais úteis onde você pode usar funções recursivas para replicar a função TRIM do Excel para remover espaços em excesso entre palavras ou bulk find e substituir valores baseados em uma lista.

Funções de consulta

Os exemplos acima definiram uma função dentro de uma consulta e depois chamaram e avaliaram a função dentro da consulta.

É também possível criar uma consulta que é uma função (uma função de consulta) e pode ser chamada e avaliada a partir de outras consultas.

let FunctionResult = (Argument1, Argument2,...) => let /*M code to evaluate in the function goes here*/ in Resultin FunctionResult

Este é o formato geral necessário para criar uma função de consulta. Nota, você precisará de um let… in… statement dentro do let… in… statement da função de consulta para executar múltiplos passos.

Conclusões

Power query é uma ótima funcionalidade no Excel que pode ajudá-lo a automatizar e simplificar sua importação e transformação de dados.

Você pode fazer muito usando apenas o ponto gráfico e clique na interface sem nunca tocar no código M. Mas à medida que seus requisitos se tornam mais avançados, pode haver um momento em que você precise editar o código M que você criou ou escrever o seu próprio a partir do zero.

Ainda nova linguagem de programação é ilegível se você não souber o básico primeiro. Este guia irá esperançosamente pô-lo a funcionar com o código M para que possa começar a criar consultas mais avançadas.