Linguagem M – O que é e como usar esse recurso no power query

A linguagem M é uma linguagem de programação desenvolvida pela Microsoft para a plataforma Power Platform.

Originalmente conhecida como “Microsoft Access Macro Language”, a linguagem M é usada para criar consultas, transformar dados e criar fórmulas avançadas em ferramentas como o Power BI, Power Query e Power Pivot.

A linguagem M é baseada em expressões de fórmulas e é semelhante a outras linguagens de programação, como o Excel e o SQL. Ela é usada para manipular dados em várias fontes de dados, incluindo bancos de dados relacionais, arquivos CSV e JSON, bem como fontes de dados na nuvem, como o Azure e o SharePoint.

A linguagem M é considerada uma linguagem de programação de alto nível e permite que os usuários criem fórmulas e consultas avançadas sem precisar de conhecimento profundo em programação

Como Declarar variáveis na Linguagem M

Na Linguagem M, você pode declarar variáveis usando a palavra-chave “let” seguida do nome da variável e do valor que você deseja atribuir a ela. Por exemplo:

let
    valorInteiro = 10,
    valorDecimal = 10.5,
    valorTexto = "Olá, Leitor do Blog!",
    valorData = #date(2024, 2, 2),
    valorDataHora = #datetime(2024, 2, 2, 14, 30, 0),
    valorHora = #time(14, 30, 0),
    valorLogico = true, // ou false
    valorLista = {1, 2, 3, 4, 5},
    valorRegistro = [Nome="Misael", Sobrenome="Soares", Idade=30],
    variavelValorTabela = Table.FromRows(
        [[1, "Produto A", 25.00], [2, "Produto B", 30.00]],
        {"ID", "Descrição", "Preço"}
    )
in
    valorTabela

Este código define várias variáveis, cada uma representando um tipo de dado diferente na Linguagem M:

• valorInteiro: Armazena um número inteiro.
• valorDecimal: Armazena um número decimal (ponto flutuante).
• valorTexto: Armazena uma cadeia de caracteres (string).
• valorData: Armazena uma data sem hora.
• valorDataHora: Armazena uma data com hora.
• valorHora: Armazena apenas a hora.
• valorLogico: Armazena um valor lógico (verdadeiro ou falso).
• valorLista: Armazena uma lista de valores.
• valorRegistro: Armazena um registro, que é similar a um objeto em outras linguagens de programação, contendo pares de nome/valor.
• valorTabela: Armazena uma tabela de dados, criada a partir de uma lista de listas para as linhas e uma lista para os nomes das colunas.

Cada tipo de variável tem sua aplicação específica e pode ser usado em diferentes contextos dentro de suas transformações de dados no Power Query. Este exemplo serve como um ponto de partida para explorar e entender como manipular diferentes tipos de dados na Linguagem M.

Como manipular arrays e objetos na Linguagem M

Neste universo do Power Query com a linguagem M, um dos aspectos fundamentais para aproveitar ao máximo dela é entender como criar e manipular arrays (listas) e objetos (registros). A seguir vamos explorar exemplos práticos que ilustram como realizar essas operações, elevando suas habilidades na Linguagem M.

Criando e Acessando Arrays

Arrays na Linguagem M são conhecidos como listas. Uma lista é uma coleção ordenada de valores, onde cada valor pode ser de qualquer tipo de dado. Vamos começar com a criação de uma lista simples.

...
listaDeNumeros = {1, 2, 3, 4, 5}
...

Para acessar um elemento específico da lista, utilizamos a sintaxe de indexação. Lembre-se de que os índices em M começam em 0.

let
    listaDeNumeros = {1, 2, 3, 4, 5},
    primeiroNumero = listaDeNumeros{0} // Retorna 1
in
    primeiroNumero

Manipulando Listas

A manipulação de listas é crucial para a transformação de dados. Você pode adicionar, remover ou modificar elementos dentro de uma lista.

Adicionando Elementos

Para adicionar elementos, utilizamos a função List.Combine.

novaLista = List.Combine({listaDeNumeros, {6, 7}})

Removendo Elementos

Para remover elementos, podemos filtrar a lista excluindo o elemento desejado.

listaSemOQuatro = List.Select(listaDeNumeros, each _ <> 4)

Modificando Elementos

Modificar elementos diretamente em uma lista é um pouco mais complexo, mas pode ser feito criando uma nova lista com os elementos modificados.

listaQuadrados = List.Transform(listaDeNumeros, each _ * _)

Exemplos de código da linguagem M para buscar dados do Google Analytics por API

Para buscar dados da internet por meio de APIs na linguagem M, você pode utilizar a função Web.Contents. Abaixo está um exemplo de código que busca dados do Google Analytics usando a linguagem M:

let
    // Configurações da API do Google Analytics
    client_id = "seu_client_id",
    client_secret = "seu_client_secret",
    refresh_token = "seu_refresh_token",
    perfil_id = "seu_perfil_id",
    // Credenciais da API do Google Analytics
    access_token = GoogleAPI.GetAccessToken(client_id, client_secret, refresh_token),
    // URL de consulta para buscar os dados do Google Analytics
    url = "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:" & perfil_id & "&start-date=30daysAgo&end-date=yesterday&metrics=ga:sessions,ga:users&dimensions=ga:date",
    // Resultado da consulta
    resultado = Json.Document(Web.Contents(url, [Headers=[Authorization="Bearer " & access_token]]))
in
    resultado

Nesse exemplo, é preciso fornecer algumas informações de configuração, como “client_id”, “client_secret”, “refresh_token” e “perfil_id”, que são usados para autenticar e autorizar a consulta à API do Google Analytics.

Depois, é necessário obter o token de acesso usando a função “GoogleAPI.GetAccessToken” e, em seguida, montar a URL de consulta para buscar os dados desejados.

No exemplo acima, estamos buscando o número de sessões e usuários nos últimos 30 dias para um perfil específico do Google Analytics. As informações são agrupadas por data (dimension=ga:date) e retornadas em um formato JSON. O resultado é então armazenado na variável “resultado” e retornado pela função.

Como dinamizar os parâmetros da Linguagem M no power query do Excel

Para deixar os campos “client_id”, “client_secret”, etc. dinâmicos no Power Query do Excel, você pode utilizar a função “Parameter” do Power Query.

Essa função permite que você defina um parâmetro que pode ser alterado em tempo de consulta e que é usado para atualizar os campos do seu código.

Para usar a função “Parameter”, siga os seguintes passos:

• Clique em “Nova fonte” na aba “Página Inicial” do Power Query.
• Selecione “Parâmetro” na lista de fontes disponíveis.
• Na janela que se abre, defina um nome para o parâmetro (por exemplo, “client_id”).
• Defina o tipo de dados do parâmetro (por exemplo, “Texto” para campos de texto).
• Clique em “OK” para criar o parâmetro.

Com o parâmetro criado, você pode usá-lo em seu código de consulta para substituir o valor fixo atual. Para isso, basta substituir o valor fixo pelo nome do parâmetro usando a sintaxe “&[nome_do_parametro]” (sem aspas). Por exemplo:

let
    client_id = #"client_id",
    client_secret = #"client_secret",
    refresh_token = "seu_refresh_token",
    perfil_id = "seu_perfil_id",
    access_token = GoogleAPI.GetAccessToken(client_id, client_secret, refresh_token),
    url = "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:" & perfil_id & "&start-date=30daysAgo&end-date=yesterday&metrics=ga:sessions,ga:users&dimensions=ga:date",
    resultado = Json.Document(Web.Contents(url, [Headers=[Authorization="Bearer " & access_token]]))
in
    resultado

No exemplo acima, os campos “client_id” e “client_secret” foram substituídos por “#client_id” e “#client_secret”, respectivamente.

Isso significa que o valor desses campos será obtido a partir dos parâmetros criados anteriormente.

Para atualizar o valor dos parâmetros, basta clicar com o botão direito do mouse no passo do parâmetro e selecionar “Editar Parâmetro”.

Em seguida, você pode alterar o valor e atualizar a consulta para ver os resultados atualizados.

Se quiser ver uma aplicação real de como consumir APIs no Power Query com paginação eu criei aqui no blog.