Usando funções personalizadas

Se você se encontrar em uma situação em que precise aplicar o mesmo conjunto de transformações a diferentes consultas ou valores, criar uma função personalizada do Power Query que possa ser reutilizado quantas vezes precisar poderá ser benéfica. 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 operadores e funções M nativas.

Você pode criar manualmente sua própria função personalizada do Power Query usando a linguagem de fórmula Power Query Mou 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.

Primeiro abordaremos as etapas básicas para criar uma função personalizada com código node interface do usuário, em seguida, nos concentraremos em usar a interface para transformar ações complexas em uma função reutilizável.

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 links para outros artigos 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 do código na interface do usuário

Nota

As etapas a seguir podem ser seguidas no Power BI Desktop ou usando a experiência do Power Query encontrada no Excel para Windows.

  1. Use a experiência do conector para se conectar aos seus dados onde eles estão hospedados. Após selecionar seus dados, selecione o botão Transformar Dados ou Editar. Isso levará você à experiência do Power Query.
  2. Clique com o botão direito do mouse em um local em branco no painel Consultas à esquerda.
  3. Selecione Consulta em branco.
  4. Na nova janela de consulta em branco, selecione o menu Página Inicial e, em seguida, Editor Avançado.
  5. Substitua o modelo pela função personalizada. Por exemplo:
    let
     HelloWorld = () => ("Hello World")
    in
     HelloWorld
    
  6. Selecione Concluído.

Para obter mais informações sobre como desenvolver funções personalizadas com a linguagem de fórmula do Power Query M, consulte este artigo: Noções básicas sobre o Power Query M Functions. Nas seções a seguir, há tutoriais que descrevem como você pode usar a interface do usuário do Power Query para desenvolver funções personalizadas sem escrever código e instruções sobre como você pode invocar sua função personalizada em sua consulta.

Criar uma função personalizada com base no tutorial de referência de tabela

Nota

O exemplo a seguir foi criado usando a experiência da á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 Folder. Para saber mais sobre o conector de Pasta, acesse 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 em que seus arquivos estão localizados e selecione Transformar Dados ou Editar. Essas etapas levam 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. Para este exemplo, a seleção foi feita para o primeiro arquivo da lista, que por acaso é o arquivo 2019.csvde abril.

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

Essa opção efetivamente cria 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 esta consulta para ser Arquivo de Exemplo.

Captura de tela exibindo a nova consulta do arquivo de exemplo.

Crie um parâmetro com o nome Parâmetro de Arquivo e o tipo de Binário. Use a consulta de arquivo de exemplo como o valor padrão e o valor atual .

Captura de tela com os valores de parâmetro de arquivo preenchidos.

Nota

Recomendamos que você leia o artigo sobre parâmetros para entender melhor como criar e gerenciar parâmetros no Power Query.

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 na caixa de diálogo Parâmetros, dentro do menu suspenso Tipo, quando você tem uma consulta que é avaliada como um binário.

É possível criar uma função personalizada sem um parâmetro. Isso geralmente é 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 a partir desses valores.

Clique com o botão direito do mouse no 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 de Arquivo.

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

Captura de tela com a consulta Transformar Arquivo de exemplo renomeada.

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

Captura de tela da opção Criar função usada para a consulta Transformar Arquivo de exemplo.

Essa operação cria efetivamente uma função que se vincula à 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.

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

Depois de criar a função, observe que um novo grupo é criado para você com o nome da função. Este 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 consulta de exemplo.
  • Sua função recém-criada, nesse caso, transforma o arquivo em.

Captura de tela 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 Transformar arquivo de exemplo. Essa consulta agora está vinculada à função Arquivo de Transformação, portanto, todas as alterações feitas nessa consulta são refletidas na função. Essa conexão é conhecida 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 destacado.

O formato de todos os arquivos CSV na pasta é o mesmo. Todos 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 para baixo, conforme mostrado na próxima imagem.

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 Transform Sample são:

  1. Remover as quatro principais linhas— essa ação se livra das linhas consideradas parte da seção de cabeçalho do arquivo.

    Captura de tela após a remoção das linhas superiores do arquivo de amostra de transformação.

    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 agora estão na primeira linha da tabela. Você pode promovê-los conforme mostrado na próxima imagem.

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

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

Nota

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

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

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 do arquivo Transform, você receberá 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 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 da função será 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.

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 será criada. Esta coluna tem valores de Tabela em suas células, conforme mostrado na próxima imagem. Para simplificar, remova todas as colunas desta tabela, exceto Name e Output Table.

Captura de tela com a função personalizada invocada e somente as colunas Nome e Tabela de Saída estão visíveis.

Nota

Para saber mais sobre como escolher ou remover colunas de uma tabela, acesse Escolher ou remover colunas.

Sua função foi aplicada a cada linha da tabela usando os valores da coluna Content 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.

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

Você pode verificar se tem dados de todos os arquivos na pasta verificando os valores na coluna Name ou Date. Para esse caso, você pode verificar os valores da 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 Combinar arquivos, mas feito manualmente.

Recomendamos que você também leia o artigo sobre Combinar arquivos — visão geral e Combinar arquivos CSV para entender melhor como a experiência de combinar 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 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 a Panamá.

Para fazer esse requisito acontecer, crie um novo parâmetro chamado Market 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 Mercado.

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

Nota

Para saber mais sobre como filtrar colunas por valores, acesse Valores de filtro.

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

Captura de tela da função agora atualizada com dois parâmetros.

No entanto, a consulta de Arquivos CSV tem um sinal de aviso ao lado dela. Agora que sua função é 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.

Captura de tela da mensagem de erro exibida após uma atualização de função.

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á.

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

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.

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. Posteriormente, essa função personalizada pode ser invocada em relação às consultas ou valores de sua escolha. Essa função personalizada pode economizar tempo e ajudá-lo 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

Captura de tela da lista original de códigos.

Você começa tendo um parâmetro que tem um valor que serve como exemplo. Para esse 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.

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

  • Origem = PTY
  • Destino = LAX
  • Companhia Aérea = CM
  • FlightID = 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órmula M do Power Query, acesse Linguagem de fórmula M do Power Query.

Em seguida, você pode transformar essa consulta em uma função fazendo um clique com o botão direito do mouse na consulta e selecionando Criar Função. Por fim, 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 invocar função personalizada preenchidos.

Depois de mais algumas transformações, você pode ver que alcançou o resultado desejado e aplicou a lógica para essa 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.