Usando funções personalizadas
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.
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.
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.
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 .
Renomeie a consulta recém-criada de File Parameter (2) para Transform Sample file.
Clique com o botão direito do mouse nesta nova consulta de arquivo Transform Sample e selecione a opção Create Function .
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.
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.
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.
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.
O próximo conjunto de etapas de transformação que precisam ser aplicadas ao arquivo de exemplo de transformação são:
Remover as quatro primeiras linhas—Esta ação elimina as linhas que são 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.
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 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.
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.
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.
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.
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.
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á.
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 .
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.
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 .
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á.
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.
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 |
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"
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.
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.
