Query “invisível”

No Power Query, a transformação de dados muitos vezes passa por criar um variado número de passos que permitem chegar ao resultado esperado. No entanto muitas vezes verificamos que temos demasiados passos ou a criação de queries auxiliares.

No processo de ETL (extract, transform and load), muitas vezes é ignorado o fato de que podemos usar queries dentro de outras queries, e também dentro do cálculo de uma coluna, e com a invocação de diversas funções agregadamente atingir o resultado esperado.

O processo de “aninhar” queries é uma das grandes vantagens da linguagem M, assim com o fato de que muitas vezes a combinação de diversas funções.

Vamos então ver um exemplo como podemos criar uma query dentro de uma coluna.

Exemplo

Objetivo: Adicionar uma coluna com o valor multiplicado pelo máximo de cada categoria

Dados: Duas colunas – Cat Value

Resultado pretendido:

Vamos apresentar as duas soluções e como podemos então reduzir o número de passos “visíveis” no nosso ETL.

Todos os passos

De modo a conseguirmos a transformação podemos realizar os seguintes passos:

• Agrupar por Cat e Máximo do Valor

• Fazer um merge da query com ela própria pela Cat

• Alterar o passo para ir buscar o passo do Changed Type em vez do agrupamento

Previous:
= Table.NestedJoin(#"Grouped Rows", {"Cat"}, #"Grouped Rows", {"Cat"}, "Grouped Rows", JoinKind.LeftOuter)

Change to
= Table.NestedJoin(#"Changed Type", {"Cat"}, #"Grouped Rows", {"Cat"}, "Grouped Rows", JoinKind.LeftOuter)

• Expandir a coluna MaximumValue da coluna Grouped Rows

• Adicionar uma coluna personalizada com o código:

[Value] * [MaximumValue]

• Eliminar a coluna Maximum Value

Como podemos verificar criamos 6 passos para fazer todas as transformações, o nosso exemplo não é muito grande e não tem grande complexidade nos passos, no entanto temos aqui dois passos que são recorrentes dentro da query (Grouped Rows e Merged Queries)

Vamos então tentar apresentar uma solução com um único passo

Um único passo

• Table.Group: cria uma tabela agrupada por categoria sendo o mesmo código utilizado na opção anterior podemos verificar pela seleção de uma das células da nossa tabela:

Table.Group(#"Changed Type", {"Cat"},
     {{"ValueMaximum", each List.Max([Value]), 
     type nullable number}})

Se verificarmos este código é o primeiro passo da opção anterior em que agrupamos por categoria isto pode ser verificado selecionando uma das células da nossa tabela.

Vamos agora proceder a alteração da nossa coluna acrescentando funções

• Table.SelectRows: permite-nos filtrar a tabela para cada categoria correspondente de modo que cada célula nos devolve um único resultado:

  Table.SelectRows (
    Table.Group(#"Changed Type", {"Cat"},
     {{"ValueMaximum", each List.Max([Value]), 
     type nullable number}}), 
    (x) =>
      x[Cat] = [Cat]
      )

Ao adicionarmos a sintaxe (x) => usamos a tabela anterior como uma variável que nos vai permitir chamar a coluna da Cat e comparar com a linha Cat da nossa tabela assim temos uma única linha por registo.

• Table.SelectColumns: Seleção da coluna que queremos para o nosso cálculo neste caso o valor máximo:

Table.SelectColumns( 
  Table.SelectRows (
    Table.Group(#"Changed Type", {"Cat"},
     {{"ValueMaximum", each List.Max([Value]), 
     type nullable number}}), 
    (x) =>
      x[Cat] = [Cat]
      ), "ValueMaximum")

• {0}[Value Maximum]: sintaxe para obter o valor de um registo dentro de uma célula especifica

Table.SelectColumns( 
  Table.SelectRows (
    Table.Group(#"Changed Type", {"Cat"},
     {{"ValueMaximum", each List.Max([Value]), 
     type nullable number}}), 
    (x) =>
      x[Cat] = [Cat]
      ), "ValueMaximum"){0}[Value Maximum]

• Multiplicação pelo Valor:

Table.SelectColumns( 
  Table.SelectRows (
    Table.Group(#"Changed Type", {"Cat"},
     {{"ValueMaximum", each List.Max([Value]), 
     type nullable number}}), 
    (x) =>
      x[Cat] = [Cat]
      ), "ValueMaximum"){0} [ValueMaximum] * [Value]

Como podemos verificar o resultado é o mesmo do anterior, no entanto temos apenas 3 passos:

Tal como referi anteriormente este exemplo não apresenta grande complexidade, no entanto demonstra flexibilidade da linguagem M e a forma como podemos combinar diferentes opções para o nosso processo de ETL ser enriquecido.

No entanto é importante referir que esta abordagem apresenta desvantagens nomeadamente no que concerne ao debug do código. Tendo um passo único, com o resultado poderá não ser simples encontrar os erros devido a não visibilidade sobre os resultados intermédios

Código:

Todos os passos:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTI0UIrVgTJN4UxjiGgSkGmKYBoimEYQZjKQaYZgmiCYUAUpQKYFgmmJYIIMiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cat = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Cat"}, {{"MaximumValue", each List.Max([Value]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Cat"}, #"Grouped Rows", {"Cat"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"MaximumValue"}, {"MaximumValue"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "Value * Maximum", each [Value] * [MaximumValue]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"MaximumValue"})
in
    #"Removed Columns"

Passo único:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTI0UIrVgTJN4UxjiGgSkGmKYBoimEYQZjKQaYZgmiCYUAUpQKYFgmmJYIIMiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cat = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Value * Maximum per Category", each Table.SelectColumns( 
  Table.SelectRows (
    Table.Group(#"Changed Type", {"Cat"},
     {{"ValueMaximum", each List.Max([Value]), 
     type nullable number}}), 
    (x) =>
      x[Cat] = [Cat]
      ), "ValueMaximum"){0} [ValueMaximum] * [Value])
in
    #"Added Custom"