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.

Criar uma função personalizada a partir do código na interface do usuário

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

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.

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.

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 .

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

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 nessa nova consulta Transformar Arquivo de exemplo e selecione a opção Criar Função.

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.

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.

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.

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.

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.

   

   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.

   

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.

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.

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.

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.

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.

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

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.

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.

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.

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 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:

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

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

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"

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.

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.