Partilhar via

Usando funções personalizadas

  • Artigo

Se você se encontrar em uma situação em que precisa aplicar o mesmo conjunto de transformações a consultas ou valores diferentes, criar uma função personalizada do Power Query que possa ser reutilizada quantas vezes precisar pode ser benéfico. Uma função personalizada do Power Query é um mapeamento de um conjunto de valores de entrada para um único valor de saída e é criada a partir de funções e operadores M nativos.

Embora possa criar manualmente a sua própria função personalizada do Power Query utilizando o código descrito em Compreender as funções do Power Query M, a interface de utilizador do Power Query oferece-lhe funcionalidades para acelerar, simplificar e melhorar o processo de criação e gestão de uma função personalizada.

Este artigo centra-se nesta experiência, fornecida apenas através da interface de utilizador do Power Query, e como tirar o máximo partido dela.

Importante

Este artigo descreve como criar uma função personalizada com o Power Query utilizando transformações comuns acessíveis na interface de utilizador do Power Query. Ele se concentra nos conceitos principais para criar funções personalizadas e links para artigos adicionais na documentação do Power Query para obter mais informações sobre transformações específicas que são referenciadas neste artigo.

Criar uma função personalizada a partir de uma referência de tabela

Nota

O exemplo a seguir foi criado usando a experiência de área de trabalho encontrada no Power BI Desktop e também pode ser seguido usando a experiência do Power Query encontrada no Excel para Windows.

Você pode acompanhar este exemplo baixando os arquivos de exemplo usados neste artigo no link de download a seguir. Para simplificar, este artigo usa o conector de pasta. Para saber mais sobre o conector de pasta, vá para Pasta. O objetivo deste exemplo é criar uma função personalizada que possa ser aplicada a todos os arquivos nessa pasta antes de combinar todos os dados de todos os arquivos em uma única tabela.

Comece usando a experiência do conector de pasta para navegar até a pasta onde seus arquivos estão localizados e selecione Transformar dados ou Editar. Estes passos levam-no à experiência do Power Query. Clique com o botão direito do mouse no valor binário de sua escolha no campo Conteúdo e selecione a opção Adicionar como nova consulta . Para este exemplo, a seleção foi feita para o primeiro arquivo da lista, que por acaso é o arquivo de abril de 2019.csv.

Captura de tela de como selecionar um arquivo para ser seu arquivo de exemplo.

Essa opção cria efetivamente uma nova consulta com uma etapa de navegação diretamente para esse arquivo como um binário, e o nome dessa nova consulta é o caminho do arquivo selecionado. Renomeie essa consulta para Arquivo de Exemplo.

Captura de tela exibindo a nova consulta Arquivo de Exemplo.

Crie um novo parâmetro com o nome File Parameter e o tipo de Binary. Use a consulta Arquivo de Exemplo como Valor Padrão e Valor Atual.

Captura de tela com os valores dos parâmetros de arquivo preenchidos.

Nota

Recomendamos que leia o artigo sobre Parâmetros para compreender melhor como criar e gerir parâmetros no Power Query.

As funções personalizadas podem ser criadas usando qualquer tipo de parâmetro. Não há nenhum requisito para que qualquer função personalizada tenha um binário como parâmetro.

O tipo de parâmetro binário só é exibido dentro do menu suspenso Tipo de diálogo Parâmetros quando você tem uma consulta avaliada como binária.

É possível criar uma função personalizada sem um parâmetro. Isso é comumente visto em cenários onde uma entrada pode ser inferida do ambiente onde a função está sendo invocada. Por exemplo, uma função que usa a data e a hora atuais do ambiente e cria uma cadeia de caracteres de texto específica a partir desses valores.

Clique com o botão direito do mouse em Parâmetro de arquivo no painel Consultas . Selecione a opção Referência .

Captura de tela com a opção Referência selecionada para o parâmetro File.

Renomeie a consulta recém-criada de File Parameter (2) para Transform Sample file.

Captura de tela com a consulta de arquivo de exemplo de transformação renomeada.

Clique com o botão direito do mouse nesta nova consulta de arquivo Transform Sample e selecione a opção Create Function .

Captura de tela da opção Criar função usada para a consulta de arquivo Transform Sample.

Essa operação cria efetivamente uma nova função que se vincula à consulta do arquivo Transform Sample. Todas as alterações feitas na consulta de arquivo Transformar exemplo são replicadas automaticamente para sua função personalizada. Durante a criação desta nova função, use Transform file como o nome da função.

Captura de tela da janela de função Criar para o arquivo Transformar.

Depois de criar a função, observe que um novo grupo é criado para você com o nome da sua função. Este novo grupo contém:

  • Todos os parâmetros que foram referenciados na consulta do arquivo Transform Sample.
  • Sua consulta de arquivo Transform Sample, comumente conhecida como consulta de exemplo.
  • Sua função recém-criada, neste caso Transformar arquivo.

Captura de ecrã do grupo de funções no painel Consultas.

Aplicando transformações a uma consulta de exemplo

Com a nova função criada, selecione a consulta com o nome Transform Sample file. Essa consulta agora está vinculada à função de arquivo Transformar, portanto, todas as alterações feitas nessa consulta são refletidas na função. Essa conexão é o que é conhecido como o conceito de uma consulta de exemplo vinculada a uma função.

A primeira transformação que precisa acontecer com essa consulta é aquela que interpreta o binário. Você pode clicar com o botão direito do mouse no binário no painel de visualização e selecionar a opção CSV para interpretar o binário como um arquivo CSV.

Captura de tela do menu suspenso binário com CSV enfatizado.

O formato de todos os arquivos CSV na pasta é o mesmo. Todos eles têm um cabeçalho que abrange as primeiras quatro primeiras linhas. Os cabeçalhos das colunas estão localizados na linha 5 e os dados começam da linha 6 para baixo, conforme mostrado na imagem seguinte.

Captura de tela dos dados CSV de exemplo antes do processamento.

O próximo conjunto de etapas de transformação que precisam ser aplicadas ao arquivo de exemplo de transformação são:

  1. Remover as quatro primeiras linhas—Esta ação elimina as linhas que são consideradas parte da seção de cabeçalho do arquivo.

    Captura de tela depois que as linhas superiores são removidas do arquivo Transform Sample.

    Nota

    Para saber mais sobre como remover linhas ou filtrar uma tabela por posição de linha, vá para Filtrar por posição de linha.

  2. Promover cabeçalhos—Os cabeçalhos da tabela final estão agora na primeira linha da tabela. Você pode promovê-los como mostrado na próxima imagem.

    Captura de tela após a primeira linha ser usada como cabeçalhos.

Depois de promover os cabeçalhos das colunas, o Power Query adiciona, por predefinição, automaticamente um novo passo Tipo Alterado que deteta automaticamente os tipos de dados para cada coluna. Sua consulta de arquivo Transform Sample se parece com a próxima imagem.

Nota

Para saber mais sobre como promover e rebaixar cabeçalhos, vá para Promover ou rebaixar cabeçalhos de coluna.

Captura de tela da consulta de exemplo de transformação final.

Atenção

Sua função de arquivo Transform depende das etapas executadas na consulta de arquivo Transform Sample. No entanto, se você tentar modificar manualmente o código para a função de arquivo Transformar, você será recebido com um aviso que diz The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'.

Invocar uma função personalizada como uma nova coluna

Com a função personalizada agora criada e todas as etapas de transformação incorporadas, você pode voltar para a consulta original onde você tem a lista de arquivos da pasta (Arquivos CSV neste exemplo). Dentro da guia Adicionar Coluna na faixa de opções, selecione Invocar Função Personalizada no grupo Geral . Dentro da janela Invoke Custom Function, insira Output Table como o nome da nova coluna. Selecione o nome da sua função, Transformar arquivo, na lista suspensa Consulta de função . Depois de selecionar a função no menu suspenso, o parâmetro para a função é exibido e você pode selecionar qual coluna da tabela usar como argumento para essa função. Selecione a coluna Conteúdo como o valor/argumento a ser passado para o parâmetro File.

Captura de tela com o botão Invocar função personalizada enfatizado com as configurações no conjunto de diálogo Invocar função personalizada.

Depois de selecionar OK, uma nova coluna com o nome Tabela de saída é criada. Esta coluna tem valores de Tabela em suas células, conforme mostrado na imagem a seguir. Para simplificar, remova todas as colunas desta tabela, exceto Nome e Tabela de Saída.

Captura de tela com a função personalizada invocada e apenas as colunas Nome e Tabela de Saída restantes.

Nota

Para saber mais sobre como escolher ou remover colunas de uma tabela, vá para Escolher ou remover colunas.

Sua função foi aplicada a cada linha da tabela usando os valores da coluna Conteúdo como argumento para sua função. Agora que os dados estão transformados na forma que você está procurando, você pode expandir a coluna Tabela de saída selecionando o ícone Expandir. Não use nenhum prefixo para as colunas expandidas.

Captura de ecrã da caixa de diálogo expandida da tabela de saída com todas as colunas da tabela selecionadas.

Você pode verificar se tem dados de todos os arquivos na pasta verificando os valores na coluna Nome ou Data . Nesse caso, você pode verificar os valores na coluna Data , pois cada arquivo contém apenas dados de um único mês de um determinado ano. Se você vir mais de um, isso significa que você combinou com êxito dados de vários arquivos em uma única tabela.

Captura de tela com a lista suspensa de data validando que a tabela final contém dados de todos os arquivos.

Nota

O que você leu até agora é fundamentalmente o mesmo processo que acontece durante a experiência Combine arquivos , mas feito manualmente.

Recomendamos que leia também o artigo sobre Visão geral de Combinar ficheiros e Combinar ficheiros CSV para compreender melhor como funciona a experiência de combinar ficheiros no Power Query e o papel desempenhado pelas funções personalizadas.

Adicionar novo parâmetro à função personalizada existente

Imagine que há um novo requisito em cima do que você construiu atualmente. O novo requisito exige que, antes de combinar os arquivos, você filtre os dados dentro deles para obter apenas as linhas em que o país é igual ao Panamá.

Para que esse requisito aconteça, crie um novo parâmetro chamado Mercado com o tipo de dados de texto. Para o Valor Atual, insira o valor Panamá.

Captura de tela do novo parâmetro com seus valores necessários.

Com esse novo parâmetro, selecione a consulta Transformar arquivo de exemplo e filtre o campo País usando o valor do parâmetro Market .

Captura de tela da caixa de diálogo de linhas de filtro com a coluna País do filtro usando o novo parâmetro Market.

Nota

Para saber mais sobre como filtrar colunas por valores, vá para Filtrar valores.

A aplicação desta nova etapa à sua consulta atualiza automaticamente a função de arquivo Transformar, que agora requer dois parâmetros com base nos dois parâmetros usados pelo arquivo Transform Sample.

Screenshot da função agora atualizado com dois parâmetros.

Mas a consulta de arquivos CSV tem um sinal de aviso ao lado. Agora que sua função está atualizada, ela requer dois parâmetros. Portanto, a etapa em que você invoca a função resulta em valores de erro, já que apenas um dos argumentos foi passado para a função de arquivo Transform durante a etapa Invoked Custom Function .

Captura de ecrã da mensagem de erro apresentada após uma atualização de função.

Para corrigir os erros, clique duas vezes em Invoked Custom Function nas etapas aplicadas para abrir a janela Invoke Custom Function . No parâmetro Market, insira manualmente o valor Panamá.

Captura de tela dos argumentos de função personalizada invocados atualizados.

Agora você pode retornar à Tabela de Saída Expandida nas Etapas Aplicadas. Verifique sua consulta para validar que apenas as linhas em que País é igual ao Panamá aparecem no conjunto de resultados finais da consulta Arquivos CSV.

Captura de tela da tabela de saída final após argumentos atualizados.

Criar uma função personalizada a partir de uma parte reutilizável da lógica

Se você tiver várias consultas ou valores que exijam o mesmo conjunto de transformações, poderá criar uma função personalizada que atue como uma parte reutilizável da lógica. Mais tarde, essa função personalizada pode ser invocada contra as consultas ou valores de sua escolha. Esta função personalizada pode poupar tempo e ajudá-lo a gerir o seu conjunto de transformações numa localização central, que pode modificar a qualquer momento.

Por exemplo, imagine uma consulta que tenha vários códigos como uma cadeia de caracteres de texto e você deseja criar uma função que decodifica esses valores, como na tabela de exemplo a seguir:

code
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

Captura de ecrã da lista original de códigos.

Você começa tendo um parâmetro que tem um valor que serve como exemplo. Para este caso, é o valor PTY-CM1090-LAX.

Captura de tela da caixa de diálogo Gerenciar parâmetros com os valores de código de parâmetro de exemplo inseridos.

A partir desse parâmetro, você cria uma nova consulta onde aplica as transformações necessárias. Para este caso, você deseja dividir o código PTY-CM1090-LAX em vários componentes:

  • Origem = PTY
  • Destino = LAX
  • Companhia aérea = CM
  • ID do voo = 1090

Captura de tela da consulta de transformação de exemplo com cada parte em sua própria coluna.

O código M a seguir demonstra esse conjunto de transformações.

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

Nota

Para saber mais sobre a linguagem de fórmulas do Power Query M, aceda a Linguagem de fórmulas do Power Query M.

Em seguida, você pode transformar essa consulta em uma função clicando com o botão direito do mouse na consulta e selecionando Criar função. Finalmente, você pode invocar sua função personalizada em qualquer uma de suas consultas ou valores, conforme mostrado na próxima imagem.

Captura de tela da lista de códigos com os valores Invoke Custom Function preenchidos.

Depois de mais algumas transformações, você pode ver que atingiu a saída desejada e aplicou a lógica para tal transformação a partir de uma função personalizada.

Captura de tela mostrando a consulta de saída final depois de invocar uma função personalizada.