5 coisas no Power Query que talvez nunca tenham te explicado ou mostrado.

5 coisas no Power Query que talvez nunca tenham te explicado ou mostrado.

O Power Query é o editor de consultas utilizado tanto no PowerBI quanto no Excel (em algumas versões) e que tem enorme poder de transformação dos dados importados de fontes diversas, sejam elas estruturadas ou não.

Usuários menos experientes utilizam o Power Query apenas com cliques nos elementos do MENU, o que sim pode ajudar bastante, mas entender as funções e detalhes que estão por trás desses cliques podem enriquecer o seu ETL, resolver problemas que nunca seriam feitos com o simples uso dos botões e te ajudarão numa certificação Microsoft.

1 - Sabendo o que está acontecendo:

Uma das coisas principais e essenciais no uso do Power Query é que, mesmo utilizando os botões no Menu, você consegue identificar que funções estão sendo utilizadas. Vou mostrar isso de duas formas:

Barra de Formulas - Dessa forma você pode acompanhar na barra de formulas cada função adicionada nas etapas criadas.

Editor Avançado - No editor avançado assim como na Barra de Formulas, você acompanhará todas as etapas inseridas.

2 - Todas as Funções em M:

No Power Query na maior parte do tempo utilizamos linguagem M, embora também você poderá ver R, SQL e Python também.

Hoje a Microsoft no MSDN traz toda a lista da sintaxe da Linguagem M em inglês, mas o Power Query editor traz escondida a listagem das funções com suas descrições em Português.

Para encontrar isso você pode dever criar uma Consulta Nula (Nova Fonte > Consulta Nula) e digitar o na barra de formulas digitar = #shared e pressionar ENTER.

"mas Rafael, não está em ordem alfabética..."

No Problema, só clicar ali no canto esquerdo superior em "Na Tabela" e ele vai converter esses Registros em uma Tabela que você vai poder colocar em Ordem.

3 - e falando em registros...

No exemplo acima eu selecionei a função Record.HasFields e você vai verificar que na função ele pede dois argumentos, um "RECORD" e um "FIELD".

E o que é "Record"?

De forma geral é um "registro de dados", ou seja, uma linha de uma tabela. Cada Tabela é composto por vários Registros.

Tabelas, Registros e Listas são as principais estruturas de dados e para compor essas estruturas utilizamos tipos primitivos que são eles:

Tipo          Valor Exemplo
Binary          00 00 00 02 // número de pontos (2)
Date          23/5/2015
DateTime      23/5/2015 13:00:00 AM
DateTimeZone  23/5/2015 13:00:00 -08:00
Duration      15:35:00
Logical          true and false
Null          null
Number          0, 1, -1, 1.5, e 2.3e-5
Text          "abc"
Time          13:34:12

Todo e qualquer dado que você encontre no Power Query é composto por esses tipos primitivos.

Quando usamos por exemplo a função Date.AddDays ela nos solicita dois argumentos, no primeiro um DateTime e no segundo um Number, ou seja, nos pede dois tipos primitivos de dados.

Vou mostrar essa função de duas formas, na primeira forma utilizando somente tipos primitivos e depois utilizando dados de uma Tabela.

Vamos criar uma Consulta Nula e digitar na barra de formulas a seguinte expressão:

= Date.AddDays(#datetime(2018, 1, 1, 08, 00, 01), 1)

Utilizei no meu tipo primitivo a expressão #datetime(2018, 1, 1, 8,0,1) que nada mais é a forma primitiva do DateTime "01/01/2018 08:00:01".

Vamos utilizar a mesma função Date.AddDays em uma tabela adicionando uma coluna Personalizada.

No lugar de utilizarmos o tipo primitivo, usamos simplesmente a coluna [Data], que é o que você normalmente utiliza na função.

4 - Você pode utilizar Variáveis:

Tanto em funções quanto dentro da própria expressão M você pode utilizar variáveis.

Abrindo uma nova consulta nula e indo no editor avançado copie e cole a expressão abaixo:

let
    a = 50,
    b = 100
in
    a + b

Você verá que trouxe o resultado 150, que é a soma das duas variáveis que utilizamos no exemplo acima.

Podemos escrever algo mais complexo para expressar a mesma sintaxe como:

let
    a = 50,
    b = 
        let
           x = 10,
           y = 10
        in 
           x * y
in        
    a + b

Note que na estrutura normal da linguagem M sempre temos a expressão"let" antes de iniciar a lista de códigos e a expressão "in" para finalizar o bloco e dizer o que queremos trazer de resultado na tela. No exemplo acima criamos um let in dentro de uma etapa de calculo do Power Query, o que é absolutamente possível e muito utilizado quando estamos fazendo algum calculo mais complexo para encontrar uma determinada variável.

Note que você pode após o "in",no editor avançado, informar o nome de qualquer das etapas antes dele e será trazido o resultado daquela etapa informada, independente de qual seja a última etapa.

5 - Crie suas próprias funções:

Uma das coisas mais empolgantes no Power Query é a criação de funções personalizadas. Elas abrem muitas possibilidades de uso da ferramenta na automatização de processos, e são especiais quando combinadas com uso de dados encapsulados (depois escrevo sobre isso).

Vou mostrar uma forma simples de criar uma Função personalizada e depois vamos invoca-la utilizando poucos passos.

5.1 - Duplicando a Tabela

Para o exemplo vamos usar a seguinte tabela de Vendas de Produto:

Na nossa situação eu quero aqui no Power Query identificar quanto percentualmente cada venda representa da venda total do dia.

Então pra identificar isso eu preciso PRIMEIRO, saber quanto foi minha venda a cada dia correto?

Vamos duplicar a nossa tabela, na área de Consultas, botão direito do mouse no nome da tabela e "Duplicar".

Ele vai criar uma nova tabela chamada fVendas (2) e nela vamos aplicar algumas transformações para encontrar primeiro, o total de vendas em todos os dias e depois selecionar um desses dias para que me traga o resultado em duas colunas (VENDAS e DATA) de um único dia escolhido sem nenhum critério, só para criarmos a nossa função.

5.2 - Criando o Fluxo de Alterações

Com a tabela duplicada, vamos excluir as colunas Cliente e Produtos que não nos interessam

Vamos então criar um agrupamento de Datas, onde o valor retornado vai ser a Soma da Coluna Vendas a Cada valor em Data.

Selecione a coluna Data, clique no cabeçalho com o botão direito do mouse e clique em "Agrupar por..."

Você vai preencher conforme demonstrado na imagem abaixo. A operação tem que ser Soma, para que some todos os valores de Venda agrupados por Data. 

O resultado será como queríamos a soma de vendas em cada dia.

Vamos filtrar o campo Data, por qualquer uma das datas, no caso vou escolher "01/02/2018".

Porque? Porque eu quis. Não importaria qual seria a data.

5.3 - Transformando em Função

Agora vem a brincadeira...como transformar essa tabela em uma função. E você vai ver que é muito simples.

Vamos abrir o editor de texto dessa fVendas (2) que acabamos de criar essa série de processos:

Na minha última etapa de "let" ele criou a seguinte expressão:

#"Linhas Filtradas" = Table.SelectRows(#"Linhas Agrupadas", each ([Data] = #date(2018, 2, 1)))

Essa é exatamente a expressão usada para filtrar linhas Table.SelectRows. Ela filtrou na coluna [Data] tudo que estivesse somente na data 1/2/2018, ou na forma primitiva, como foi escrita automaticamente pelo sistema, #date(2018, 2, 1).

Vamos alterar esse #date(2018, 2, 1) por uma variável que vamos chama-la DataSelecionada:

E nossa expressão vai ficar assim:

    #"Linhas Filtradas" = Table.SelectRows(#"Linhas Agrupadas", each ([Data] = DataSelecionada))

Se você já deu um concluído, vai ver que houve um erro na sua expressão, porque não dissemos ainda que DataSelecionada se trata de uma variável de Função.

Para fazer isso no nosso editor avançado antes da expressão "let" lá no início, vamos incluir:

(DataSelecionada as date) => 

No editor avançado ficará como na foto abaixo:

Agora sim amiguinho, clique em concluído e você vai ver que algo diferente aconteceu e não aparece mais uma tabela, mas sim uma tela solicitando para você Inserir um Parâmetro.

5.4 - Invocando a Função Personalizada

Bem temos nossa função personalizada criada e vamos invoca-la na nossa tabela fVendas:

Com fVendas selecionado vamos em Adicionar Coluna > Invocar Função Personalizada.

E aí temos a possibilidade de dizer qual o nome da função e qual coluna, ou valor, queremos que seja a variável daquela função (poderia ser mais de uma variável).

Nessa nova coluna criada será apresentada em cada linha uma tabela contendo Data (com valor igual a Data que já temos na nossa tabela principal) e VendaSoma, que é o total de Venda no dia informado na linha.

Vamos expandir essa Tabela, mas somente selecionando a coluna VendaSoma.

E agora só brincar de dividir a Coluna Vendas pela Coluna VendaSoma, o que você pode fazer adicionando uma Coluna Personalizada com a seguinte expressão:

= [Vendas]/[VendaSoma]

Sim uma divisão simples, para encontrarmos o Percentual da venda para o total do dia.

E no final nossa tabela com a coluna Percentual.

E se você quer saber mais dicas sobre Power BI que tal conhecer o meu canal no youtube?

https://www.youtube.com/rafaelmendonça

Ficou alguma dúvida ou considera outras coisas mais interessantes, manda aí nos comentários.

Grande abraço e até a próxima.