Usar funções personalizadas

Se você se deparar com uma situação em que precise aplicar o mesmo conjunto de transformações a diferentes consultas ou valores, a criação de uma função personalizada do Power Query que possa ser reutilizada quantas vezes precisar poderá ser útil. Uma função personalizada do Power Query é um mapeamento de um conjunto de valores de entrada em um único valor de saída e é criada com base em operadores e funções de M nativas.

Embora você possa criar manualmente sua própria função personalizada do Power Query usando código, conforme descrito em Noções básicas de funções da M do Power Query, a interface do usuário do Power Query oferece recursos para acelerar, simplificar e aprimorar o processo de criação e gerenciamento de uma função personalizada.

Este artigo se concentra nessa experiência, fornecida apenas por meio da interface do usuário Power Query, e mostra como aproveitar isso ao máximo.

Importante

Este artigo descreve como criar uma função personalizada com o Power Query usando transformações comuns acessíveis na interface do usuário do Power Query. Ele se concentra nos principais conceitos para criar funções personalizadas e oferece 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 com base em uma referência de tabela

Observação

O exemplo a seguir foi criado usando a experiência da área de trabalho disponível 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 Pasta. Para saber mais sobre o conector Folder, acesse Folder. 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 Folder para navegar até a pasta em que seus arquivos estão localizados e selecione Transformar Dados ou Editar. Essas etapas levarão você à experiência do Power Query. Clique com o botão direito do mouse no valor Binário de sua preferência no campo Conteúdo e selecione a opção Adicionar como Nova Consulta. Neste exemplo, a seleção foi feita para o primeiro arquivo da lista, que por acaso é o arquivo April 2019.csv.

Essa opção cria efetivamente uma 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 como Arquivo de Exemplo.

Crie um novo parâmetro com o nome Parâmetro de Arquivo e o tipo Binário. Use a consulta de Arquivo de Exemplo como Valor Padrão e Valor Atual.

Observação

Recomendamos que você leia o artigo sobre Parâmetros para entender melhor como criar e gerenciar 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 uma função personalizada tenha um binário como parâmetro.

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

É possível criar uma função personalizada sem um parâmetro. Isso é frequentemente visto em cenários em que uma entrada pode ser inferida do ambiente em que 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 com base nesses valores.

Clique com o botão direito do mouse no Parâmetro de Arquivo no painel Consultas. Selecione a opção Referenciar.

Renomeie a consulta recém-criada de Parâmetro de Arquivo (2) para Transformar Arquivo de exemplo.

Clique com o botão direito do mouse nesta nova consulta Transformar Arquivo de exemplo e selecione a opção Criar Função.

Essa operação efetivamente cria uma função que é vinculada à consulta Transformar Arquivo de exemplo. Todas as alterações feitas na consulta Transformar Arquivo de exemplo são replicadas automaticamente para sua função personalizada. Durante a criação dessa função, use Transformar arquivo como o Nome da função.

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

• Todos os parâmetros que foram referenciados em sua consulta Transformar Arquivo de exemplo.
• Sua consulta Transformar Arquivo de exemplo, comumente conhecida como a consulta de exemplo.
• Sua função recém-criada, nesse caso, Transformar arquivo.

Aplicar transformações a uma consulta de exemplo

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

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

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

O próximo conjunto de etapas de transformação que precisa ser aplicado à consulta Transformar Arquivo de exemplo é:

1. Remover as quatro primeiras linhas – essa ação se livra das linhas que são consideradas parte da seção de cabeçalho do arquivo.

   

   Observação

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

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

   

Depois de promover seus cabeçalhos de coluna, o Power Query, por padrão, adiciona automaticamente uma nova etapa Tipo Alterado que detecta automaticamente os tipos de dados em cada coluna. Sua consulta Transformar Arquivo de exemplo ficará semelhante à próxima imagem.

Observação

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

Cuidado

Sua função Transformar arquivo depende das etapas executadas na consulta Transformar Arquivo de exemplo. No entanto, se você tentar modificar manualmente o código para a função Transformar arquivo, vai se deparar com um aviso que menciona 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

Agora que a função personalizada está criada e todas as etapas de transformação incorporadas, você pode voltar para a consulta original em que 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 Invocar Função Personalizada, insira Tabela de Saída como 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ê poderá 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 de Arquivo.

Depois de selecionar OK, uma coluna com o nome Tabela de Saída é criada. Esta coluna tem valores Table em suas células, conforme mostrado na próxima imagem. Para simplificar, remova todas as colunas dessa tabela, exceto Nome e Tabela de Saída.

Observação

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 que usa os valores da coluna Conteúdo como o argumento para sua função. Agora que os dados sã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.

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

Observação

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

Recomendamos que você também leia o artigo sobre Visão geral de combinação de arquivos e Combinar arquivos CSV para entender melhor como a experiência de combinação de arquivos funciona no Power Query e o papel que as funções personalizadas desempenham.

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

Imagine que há um novo requisito além do que você criou no momento. 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 a Panamá.

Para fazer colocar esse requisito em prática, crie um parâmetro chamado Mercado com o tipo de dados de texto. Para o Valor Atual, insira o valor Panamá.

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

Observação

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

A aplicação dessa nova etapa à consulta atualiza automaticamente a função Transformar arquivo, que agora exige dois parâmetros com base nos dois parâmetros usados pela consulta Transformar Arquivo de exemplo.

Mas a consulta de Arquivos CSV tem um sinal de aviso ao lado dela. Agora que a função foi 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 Transformar arquivo durante a etapa Função Personalizada Invocada.

Para corrigir os erros, clique duas vezes em Função Personalizada Invocada nas Etapas Aplicadas para abrir a janela Invocar Função Personalizada. No parâmetro Mercado, insira manualmente o valor Panamá.

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

Criar uma função personalizada com base em uma parte de lógica reutilizável

Se você tiver várias consultas ou valores que exijam o mesmo conjunto de transformações, você poderá criar uma função personalizada que atue como uma parte de lógica reutilizável. Posteriormente, essa função personalizada pode ser invocada nas consultas ou valores de sua escolha. Essa função personalizada pode economizar tempo e ajudar você a gerenciar seu conjunto de transformações em um local central, que você 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ê queira criar uma função que decodifica esses valores, como na tabela de exemplo a seguir:

código
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

Você começa com um parâmetro que tenha um valor que sirva de exemplo. Para esse caso, é o valor PTY-CM1090-LAX.

Com base nesse parâmetro, você cria outra consulta em que aplica as transformações necessárias. Para esse caso, você deseja dividir o código PTY-CM1090-LAX em vários componentes:

• Origem = PTY
• Destino = LAX
• Airline = CM
• FlightID = 1090

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"

Observação

Para saber mais sobre a linguagem de fórmulas do Power Query M, acesse 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. Por fim, você pode invocar a função personalizada em qualquer uma de suas consultas ou seus valores, conforme mostrado na próxima imagem.

Depois de mais algumas transformações, você poderá ver que alcançou a saída desejada e aplicou a lógica dessa transformação de uma função personalizada.