Usando funções personalizadas

Se se encontrar numa situação em que precisa de aplicar o mesmo conjunto de transformações a diferentes consultas ou valores, criar uma função personalizada do Power Query que possa ser reutilizada tantas vezes quanto 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 nativos M.

Pode criar manualmente a sua própria função personalizada Power Query usando a linguagem de fórmulas Power Query M, ou a interface Power Query oferece funcionalidades para acelerar, simplificar e melhorar o processo de criação e gestão de uma função personalizada.

Primeiro, abordaremos as etapas básicas para a criação de uma função personalizada com código na interface do utilizador, depois concentrar-nos-emos em usar a interface para transformar ações complexas numa função reutilizável.

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

1. Utilize a experiência do conector para se conectar aos seus dados onde estão armazenados. Quando tiver selecionado os dados, selecione o botão Transformar Dados ou Editar. Isto vai levá-lo à experiência Power Query.

2. Clique com o botão direito do rato num espaço em branco no painel Consultas à esquerda.

3. Selecione consulta em branco.

4. Na nova janela de consulta em branco, selecione o menu Início e depois Editor avançado.

5. Substitua o modelo pela sua função personalizada. Por exemplo:

   let
    HelloWorld = () => ("Hello World")
   in
    HelloWorld
   

6. Selecione Concluído.

Para mais informações sobre o desenvolvimento de funções personalizadas com a linguagem de fórmulas Power Query M, consulte este artigo: Compreender as Funções M do Power Query. Nas secções seguintes, há tutoriais que descrevem como pode usar a interface do Power Query para desenvolver funções personalizadas sem escrever código, e instruções sobre como pode invocar a sua função personalizada na sua consulta.

Criar uma função personalizada a partir de um tutorial sobre referências de tabelas

Você pode acompanhar este exemplo baixando os arquivos de exemplo usados neste artigo a partir do seguinte link de download . Para simplificar, este artigo usa o conector de pasta. Para saber mais sobre o conector 'Folder', consulte 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 funcionalidade do conector de pasta para navegar até a pasta onde os seus arquivos estão localizados e escolha Transformar Dados ou Editar. Estes passos levam-no à experiência Power Query. Clique com o botão direito do mouse no valor binário de sua escolha no campo de conteúdo e selecione a opção Adicionar como nova consulta opção. Para este exemplo, a seleção foi feita para o primeiro ficheiro da lista, que é, por acaso, o ficheiro Abril 2019.csv.

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 ser Arquivo de Exemplo.

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

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

Renomeie a consulta recém-criada de Parâmetro de Ficheiro (2) para Ficheiro de Exemplo de Transformação.

Clique com o botão direito do mouse neste novo arquivo de exemplo consulta e selecione a opção Criar função.

Esta operação efetivamente cria uma nova função que estabelece ligação com a consulta do arquivo Transform Sample. Todas as alterações que fizer na consulta do ficheiro Transform Sample são replicadas automaticamente para a sua função personalizada. Durante a criação desta nova função, use arquivo Transform 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. Este novo grupo contém:

• Todos os parâmetros que foram referenciados na consulta do seu arquivo Transform Sample.
• Seu ficheiro de consulta Transform Sample, comumente conhecido como a consulta de exemplo .
• A sua função recém-criada, neste caso, de Transformar o arquivo .

Aplicando transformações a uma consulta de exemplo

Com a nova função criada, selecione a consulta com o nome Transform Sample file. Esta consulta está agora vinculada à função do arquivo Transform , portanto, todas as alterações feitas a esta 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.

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

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 linhas superiores—Esta ação elimina as 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 de linha, vá a Filtrar uma tabela 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.

   

Depois de promover os cabeçalhos das suas colunas, o Power Query por padrão adiciona automaticamente uma nova etapa Tipo Alterado que deteta automaticamente os tipos de dados de cada coluna. A sua consulta no ficheiro Transform Sample parece-se com a 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 onde você tem a lista de arquivos da pasta (Arquivos CSV neste exemplo). Dentro do separador Adicionar Coluna na barra de ferramentas, selecione Invocar Função Personalizada no grupo Geral. Dentro da janela Invoke Custom Function, insira Tabela de Saída como nome da nova coluna . Selecione o nome da sua função, Transform file, na lista suspensa Consulta de funções. 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 Content como o valor/argumento a ser passado para o File Parameter.

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

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.

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 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 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 a do país é igual a Panamá.

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

Com este novo parâmetro, selecione a consulta do arquivo Transform Sample e filtre o campo Country usando o valor do parâmetro Market.

A aplicação desta nova etapa na sua consulta atualiza automaticamente a função do arquivo de Transformação, que agora requer dois parâmetros, com base nos dois parâmetros que o arquivo de Exemplo de Transformação usa.

Mas o arquivos CSV consulta tem um sinal de aviso ao lado. Agora que sua função está atualizada, ela requer dois parâmetros. Portanto, a etapa em que se invoca a função resulta em valores de erro, já que apenas um dos argumentos foi passado para a função Transform 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 Market, insira manualmente o valor "Panama".

Agora podes retornar à Tabela de Saída Expandida nos Passos Aplicados. Verifique a sua consulta para validar que apenas as linhas nas quais o país é igual a Panamá aparecem no conjunto de resultados finais 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. 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:

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

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

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

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.