Power Query: Criar funções personalizadas!
Neste artigo vais aprender a criar funções personalizadas no Power Query, para para que possas transformar e adaptar os teus dados às tuas necessidades específicas. Vamos lá?
O Power Query é uma das ferramentas mais poderosas do Microsoft Excel! A utilização de funções personalizadas, por exemplo, permite-nos guardar a informação de transformações que aplicamos a uma determinada consulta e voltar a reaplicá-las noutras consultas futuras.
Para podermos criar uma função personalizada, os passos são relativamente simples:
- Passo 1: Utilizar uma consulta e aplicar as respetivas transformações
- Passo 2: Converter os passos numa função
- Passo 3: Invocar a função para aplicar noutra consulta
Não percas aqui todo o processo detalhado e aprende a utilizar o Power Query para criares as tuas funções personalizadas. Alguma dúvida envia-nos a tua mensagem... ficamos à tua espera!
Passo 1: Utilizar uma consulta e aplicar as respetivas transformações
Vamos começar por importar um conjunto de dados e executar algumas transformações nesses dados. O exemplo a aplicar vai ser através do Power Bi Desktop, mas pode perfeitamente ser feito também no Microsoft Excel.
Começamos por Importar os dados para o Power Query através do menu Obter Dados [Get Data] e optamos por um ficheiro de Texto / CSV.
Escolhemos o ficheiro e optamos por transformar os dados:
De seguida, já no Power Query iniciamos o processo de transformações, nas quais vou exemplificar por imagens algumas transformações:
- Remover as colunas desnecessárias:
- Acrescentar uma nova coluna calculada através da multiplicação de 2 colunas (Quantidade e Valor Unitário)
- Extrair informação do Mês e do Ano, da data de transação
- Retiramos os últimos 7 caracteres para a data surgir no formato “mm/aaaa”.
- Agrupar a informação por Mês / Ano
- Ordenar os dados da Tabela por mês / ano
Passo 2: Converter as transformações numa função personalizada
Para converter os passos aplicados numa função personalizada basta acedermos ao Editor Avançado [Advanced Editor] no menu Base, onde é possível consultar todo o código gerado para transforma a consulta.
A conversão de um conjunto de passos para função faz-se através do seguinte pedaço de texto:
()=>
Entre os “parêntesis” são colocados os parâmetros da função, da mesma forma que uma função no Excel contem argumentos. Esses argumentos devem depois ser passados para a consulta substituindo o parâmetro (Argumento) pelos valores da consulta.
Neste caso a função irá apenas substituir o caminho de origem da consulta (um valor estático) por um valor dinâmico representado pelo parâmetro: “CaminhoBase”, que deve substituir o caminho original da consulta…
Confirmando a edição ao pressionar o botão “Concluído”, os passos são convertidos numa função:
Passo 3: Invocar a função para efetuar as transformações
A função pode ser invocada diretamente numa nova consulta (Query) ou através de uma coluna calculada.
Diretamente numa nova consulta basta aceder a uma Nova Origem [New Source] com uma Consulta em branco [Blank Query].
Com uma coluna calculada é possível aplicar a função a vários registos (Ficheiros) em simultâneo.
Começamos por obter dados de uma Pasta [Folder]
Identificamos o caminho da pasta… e acedemos à transformação dos dados.
Dos dados necessitamos apenas do caminho da pasta e do nome do ficheiro, removendo todas as restantes colunas.
De seguida convertemos as duas colunas numa só, através do Botão Intercalar Consultas [Merge Queries].
Esta opção permite ter uma coluna, que tem o caminho de cada um dos ficheiros, que corresponde ao parâmetro pretendido pela função para executar as transformações.
Para Invocar a função, acedemos ao menu Adicionar Colunas [Add Columns] e ao botão Invocar Função Personalizada [Invoque Custom Function].
A função retorna uma tabela com as transformações implementadas para cada um dos ficheiros.
Para terminar a consulta podemos remover a coluna “Intercalado” e expandir a coluna “Combinar”, juntando todas as tabelas numa única consulta.
O resultado final é apresentado.
Outros conteúdos relevantes:
Usar a Função nova LAMBDA para reutilizar cálculos e conjugações
Confira como a função LAMBRA, do Microsoft Excel, vai revolucionar a forma como realizamos operações, reutilizando cálculos ou conjugações de fórmulas.
Função MATCH - Um complemento para pesquisas!
Imagine que a sua folha de trabalho contém 2500 registos ou mais, e que a informação do produto consta em qualquer...