FUNCTION ou USER DEFINED FUNCTIONS, são funções criadas para ações repetitivas, ou seja, se vc quer empacotar uma quantidade de ações, ou “applied steps” num lugar e garantir que esses passos sejam aplicados em outra query, vc pode fazer isso através de FUNCTION, (funções).
Uma dificuldade que eu tive pra entender Functions é de que vc precisa abstrair um pouco a forma de funcionamento, ou seja, é importante ter uma visão mais macro. O que eu quero dizer é, se vc criar uma função pra um contexto específico, como por exemplo, criar uma função que pega uma tabela, faz um join e retorna uma coluna nova, então vc fica pensando, será que essa função vai funcionar em outra tabela? A resposta é… Se a sua tabela tiver a mesma estrutura da sua tabela origem, ou tabela modelo, então a função vai sim funcionar. E é com isso que precisamos nos preocupar, com o modelo.
Função Manual Simples
Então vamos criar uma função que faz isso, que pega alguma coisa, faz um join e retorna uma coluna nova.
Por exemplo, temos uma listagem de pessoas por cidades e Siglas de Estado, tipo PEDRO, VITORIA, ES, ou seja, uma tabelinha com três colunas, Nome, Cidade, UF… E vc precisa adicionar uma coluna de Nome do Estado, ou seja, o resultado deveria ficar assim: PEDRO, VITORIA, ES, ESPIRITO SANTO. Vc pode fazer isso usando função.
Primeiro, vc tem que criar o exemplo, o modelo. Pra isso precisamos primeiro fazer na mão o que queremos. Então vamos pegar uma tabela de pessoas e fazer um join com minha tabela de estados.
Tabela de PESSOA1
Tabela de ESTADO (tem todos 26 estados + DF)
Daí, vamos fazer o merge, ou seja, o left join (dupliquei PESSOA_ESTADO1 antes do merge, só pra garantir).
O resultado fica assim:
Agora vou expandir a coluna UF_ESTADO:
Só que eu só preciso da coluna Unidade da Federação, então vou fazer um ajuste aqui pra tirar a colunad e UF.1 e renomear Unidade da Federação pra NomeEstado.
O resultado ficou assim:
Então se liga, até agora o que a gente tem é o modelo pronto. Esse modelo tem que virar nossa função, mas pra isso virar uma função precisamos de um parâmetro de fonte de dados, afinal, esse modelo que criamos aponta diretamente pra fonte de dados dele.
Então, vamos manualmente ajustar o código M, que estava originalmente assim:
Vai ficar assim:
O que eu fiz aqui foi trocar o nome da fonte de PESSOA_ESTADO1 para Source, sendo que o “(Source) =>” indica que Source passa a ser uma variável (power query chama variável de parâmetro, então esses dois termos significam a mesma coisa aqui).
Beleza, o que eu recebi foi o seguinte:
Temos uma função que chamei de fMERGE, agora é só usar.
Pra usar essa função só tem um jeito, vc precisa manualmente ir na tabela e criar um novo passo e adicionar essa função pra usar esse nome passo, assim:
Vai na tabela PESSOA_ESTADO1, vai em applied steps, clica com botão direito, seleciona “insert step after”. Isso vai criar um passo novo chamado Custom1. Daí vai na barra de fórmulas e o resultado que aparecer lá vc poe dentro de fMERGE(). Veja na imagem:
Ao apertar enter o resultado é esse: que se vê na imagem acima, perceba que agora temos uma coluna nova, chamada NomeEstado.
Função Manual mais complexa
Como o primeiro exemplo foi muito manual vou tentar descrever um outro exemplo aqui um pouco mais complexo, mas ainda manual.
Vamos usar um outro exemplo… Vamos supor que vc tem N arquivos com a mesma estrutura e vc quer aplicar uma transformação em um arquivo modelo e replicar essas transformações para quaisquer outros N arquivos. Nesse caso você pode criar uma função.
Nesse exemplo temos 3 arquivos de vendas. Janeiro, Fevereiro e Março e quero tratar eles de uma forma, usando função, ou custom function, ou user defined function.
Pra isso eu vou importar um arquivo qualquer que eu queira usar como modelo, nesse caso vai ser janeiro.
Vou tratar o arquivo de Janeiro e a partir dele vou criar um modelo.
Depois vou carregar minha pasta onde tem todos os meus 3 arquivos (janeiro, fevereiro e março) e vou aplicar esse modelo de transformações em todos arquivos dessa pasta.
Então vamos começar:
Primeiro, vamos importar Janeiro.
Na imagem acima eu já importei Janeiro e fiz todas modificações que eu queria, ou seja, ele já está pronto pra se tornar meu modelo.
Agora preciso ajustar o código M desse meu arquivo, bem parecido com o que fizemos no primeiro exemplo. Mas aqui eu vou remover a parte de File.Contents() e substituir por uma variável (ou parâmetro, são sinônimos). Vou chamar de ArquivoBinario.
O código M vai ficar assim, disso:
Para isso:
Logo depois que vc der OK nesse código alterado vc vai ver que seu arquivo modelo virou uma função!
Agora é só testar a função… Pra ver se funciona eu vou carregar meu Folder com todos meus 3 arquivos de Janeiro, Fevereiro e Março e ver se eles vão ficar todos com a mesma cara do meu arquivo modelo.
Pra importar folder:
Na imagem acima vc pode ver que tenho 3 linhas, e nessas linhas tem essa coluna aí de Content que guarda um conteúdo binário. Se vc prestou atenção sabe que nossa função aceita um argumento do tipo binário.
O que vamos fazer agora é invocar a custom function dentro dessa nossa query aí de folder dos arquivos.
Então eu fui em Add Column > Invoke Custom Function > Selecionei a função_Modelo e apliquei ela na coluna Content.
O resultado é esse:
O Power BI cria uma coluna nova, que é a função_Modelo. Pra adiantar as coisas aqui, eu vou remover algumas colunas e expandir a função_Modelo. Olha como fica:
Legal, né?!
Ou seja, usei uma função que aplicou aqueles passos que eu criei no nosso modelo. Agora todos meus arquivos que estiverem nessa pasta e forem desse formato vai estar automaticamente ajustados.
Função Não-Manual
Esse mesmo exemplo que eu usei acima das vendas pode ser feita de uma outra forma, mais trabalhosa, mas menos suscetível a falhas e mais fácil de dar manutenção. É o que chamamos de Função Automática… mas vamos lá…
O conceito é o mesmo, precisamos de um modelo, de criar uma função com base nesse modelo, só que como não vamos criar a variável no código do editor avançado, aqui vamos precisar de criar um parâmetro… A dificuldade mora em conseguir transmitir para esse parâmetro o valor que queremos e por isso precisamos de fazer alguns passos há mais.
Aqui nosso primeiro passo vai ser puxar o folder inteiro. Vou chamar essa query que trás o folder completo de 1 – Vendas Mensais.
Daí, desse resultado eu preciso pegar e criar “como nova query” uma query a partir de um binário desse meu “1 – Vendas Mensais”.
Aqui o vendas mensais:
Esse resultado do Add as New Query vou chamar de 2 – Modelo Binario.
A partir desse modelo binário eu crio um parâmetro, é simples, só criar um parâmetro tipo binário.
Agora vou criar a função. Não daria pra criar antes pq a function depende do parâmetro.
Pra criar a função vou criar uma referência do meu modelo binário (2). Vou chamar essa minha função de 4 – Arquivo Modelo.
Daí vai na formula dessa função e como ela é uma referência vc vai ver que está assim na fórmula = #”2 – Modelo Binario”. Substitua isso pelo seu parâmetro. Vai ficar assim:
= #”3 – Parametro Binario”
A função ainda não é uma função… tá vendo que ela é apenas uma tabela? Então… pra virar uma função de verdade vc precisa clicar com botão direito em cima dela e criar em Create Function e dar um nome, vou chamar de 5 – Funcao Modelo.
Se vc olhar o código dessa função vai ver que é bem parecido com o código do exemplo “manual complexo”, que falamos antes, olha só:
= (#”3 – Parametro Binario” as binary) =>
let
Source = Csv.Document(#”3 – Parametro Binario”,[Delimiter=”,”, Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Column1”, type text}, {“Column2”, type text}, {“Column3”, type text}, {“Column4”, type text}, {“Column5”, type text}, {“Column6”, type text}, {“Column7”, type text}, {“Column8”, type text}, {“Column9”, type text}, {“Column10”, type text}, {“Column11”, type text}, {“Column12”, type text}})
in
#”Changed Type”
Vc nao precisa saber desse código, só quis mostrar por curiosidade.
Até agora estamos assim:
Pronto, praticamente terminou. O que falta agora é tratar o arquivo modelo (4) e aplicar a função na query de vendas mensais (1).
Vamos fazer isso então.
Primeiro eu vou aplicar os ajustes que quero no arquivo modelo (4).
Vai ficar assim:
E agora vou aplicar a função customizada no meu vendas mensais (1).
Se liga que eu to aplicando a função na coluna Content pq é nela que tem as informações binárias que o parâmetro da nossa função precisa.
O resultado é uma coluna nova lá no final da query. Lembrando que estamos na vendas mensais (1). Olha só:
O que eu vou fazer é remover algumas colunas e expandir a função modelo.
O resultado é que agora temos todos arquivos da nossa pasta tratados de acordo com nosso arquivo modelo!
Links de apoio:
Pedro Carvalho 