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:
MS Excel: Criar um Formulário para formatar texto!
Neste vídeo, vamos fazer um formulário para criar algumas formatações tradicionais de texto, para formatar pequenos intervalos de células. Vamos lá?
Os vídeos mais vistos de 2022
Com o ano de 2022 prestes a terminar, nada melhor do que fazermos uma retrospetiva deste ano, para nos prepararmos para um novo ano repleto de oportunidades.