Funções de consulta personalizada do LuckyTemplates

Para o tutorial de hoje, vamos criar uma função de consulta personalizada do LuckyTemplates que lidará com um formato de dados empilhados comum e difícil de manipular. Essa técnica é facilmente reutilizável em vários relatórios e várias tabelas. Você pode assistir ao vídeo completo deste tutorial na parte inferior deste blog.

Esta é uma continuação do tutorial que fiz recentemente, onde lidamos com um formato comum de difícil manuseio de dados empilhados e usamos Modulo, Pivot e algumas funções de limpeza com o Power Query para transformar esses dados em um formato organizado e funcional.

É muito provável que você precise repetir esse processo. Se esse tipo de formato resultou de um copiar e colar, provavelmente acontecerá novamente. Portanto, nesse caso, você provavelmente desejará criar uma função personalizada que será reutilizável em vários relatórios e várias tabelas, independentemente de como são nomeados e do que mais há nessas tabelas.

Índice

• Como criar e depurar funções de consulta personalizada do LuckyTemplates
• Conclusão

Como criar e depurar funções de consulta personalizada do LuckyTemplates

Vamos pular para o Power Query sobre isso. Se formos para nossa tabela de Dados, no Editor Avançado, temos o código M de como acabamos indo daquela pilha de dados para o formato desejado.

Se quisermos reutilizar isso, basta clicar com o botão direito do mouse em Data e dizer Create Function .

Ele diz que não fará referência a nenhum parâmetro, mas não há problema em criar uma função sem parâmetros. Nós vamos responder, Sim, porque vamos adicionar os parâmetros à medida que avançamos no Editor Avançado. Chamaremos essa função de Unstack – 3 Elements , apenas porque temos as três linhas no formato bruto. Se tivéssemos um formato de duas linhas, provavelmente construiríamos um separado com dois ou quatro elementos.

Então agora, temos este Invoke, não temos parâmetros, e vamos para o Editor Avançado e começamos a trabalhar nele.

Primeiro, não precisamos dessa Fonte. Vamos usar os parâmetros da função para definir nossa fonte.

Queremos definir o parâmetro que será inserido. Fazemos isso com um parêntese aberto e, em seguida, um nome de parâmetro. Vamos chamar isso de Stack , que será aquela coluna de dados que é a pilha de dados não formatada que veio da colagem dos endereços de e-mail.

Queremos que isso venha como uma lista e queremos que isso resulte em uma tabela porque, à medida que o dinamizamos de uma única coluna para várias colunas, ele vai de uma lista para uma tabela. E agora, precisamos converter essa lista recebida em uma tabela para que possamos adicionar nosso índice, iniciar nosso módulo não dinâmico e fazer todas as coisas necessárias para reformatá-lo.

Vamos chamá-lo de ConvertStack , e ele usará uma função chamada Table.FromList . Como muitas das funções M, ele faz exatamente o que diz. Leva apenas uma lista. Neste caso, a lista será nossa lista Stack. E então, precisamos substituir o Source aqui pelo nosso ConvertStack. Parece muito bom! Não temos erros de sintaxe, então vamos clicar em Concluído.

Agora está nos dando o que queríamos, que é a possibilidade de escolher uma coluna.

Então, vamos escolher TestData, que são os dados mal formatados. Escolheremos a coluna Value e, em seguida, clicaremos em OK. Depois disso, clicamos em Invocar.

No entanto, recebemos esse erro. É realmente difícil identificar o que é esse erro. Um dos problemas com as funções personalizadas é que elas não quebram as etapas aplicadas, você apenas obtém uma etapa para toda a função personalizada. Mas há uma maneira de contornar isso na depuração que mostrarei a você. Isso tornará muito mais fácil descobrir o que está acontecendo aqui.

Em vez de executar isso por enquanto como uma função, vamos executá-lo como uma consulta. Vamos apenas iniciar manualmente essa chamada de pilha. Temos que definir o que é Stack porque não o estamos desenhando por meio da interação de escolhê-lo no Invoke. Portanto, temos Stack igual a TestData e a coluna Value de TestData.

O que ele faz é dividi-lo agora nas Etapas Aplicadas. Isso nos ajudará a descobrir por que isso não está funcionando. Observe uma coisa interessante quando extraímos Stack. Inicialmente, ele extrai TestData[Value], mas quando o convertemos em uma tabela, em vez de Value ser o cabeçalho da coluna, o cabeçalho da coluna agora é Column1.

Lembre-se da mensagem de erro, foi que não foi possível encontrar a coluna Valor. A razão pela qual não foi possível localizar a coluna Value é que a função Table.FromList a renomeia para Column1.

Se descermos as etapas aplicadas, você pode ver que é na etapa personalizada adicionada que obtemos o erro. É aqui que o Value do registro não foi encontrado.

E assim, se formos ao Editor avançado, encontramos esse campo personalizado e podemos ver que está no Text.Remove onde estamos tirando os caracteres inúteis que não precisamos, mas ainda está se referindo a o campo Valor. Vamos mudar isso para Column1. Da mesma forma, no campo Colunas removidas, refere-se a Valor, portanto, também o alteraremos para Coluna1.

E quando chegamos ao final dos Passos Aplicados, produz o resultado final perfeito.

Agora que sabemos que está funcionando bem, só temos mais uma coisa a fazer. Lembre-se de que, para depurá-lo, transformamos a função personalizada em uma consulta. E então, agora precisamos pegar essa consulta e transformá-la novamente em uma função.

Criando uma função de limpeza de texto personalizada no
código M do Power Query em colunas personalizadas do Power Query | Consulta de funções e operadores do LuckyTemplates
no Editor de consultas

Conclusão

Neste tutorial, criamos essa função personalizada, depuramos, testamos e está funcionando muito bem. E agora, sempre que tivermos uma pilha de três elementos, podemos apenas selecionar essa tabela, escolher essa coluna dentro da tabela e executar essa função personalizada. Em seguida, aplique o Modulo, o unpivot, e faça a limpeza.

Isso deve fornecer algumas ferramentas interessantes em termos de criação de funções personalizadas. Além disso, lembre-se daquele truque sobre depuração, transformando-o novamente em uma consulta de uma função e, em seguida, alternando-o novamente após a depuração. É uma ferramenta realmente valiosa que facilita muito a depuração de funções personalizadas.

Tudo de bom!