E aí, pessoal, tudo bem?
Você já precisou substituir valores no Power Query e teve que criar diversas etapas e ficou se perguntando se era possível realizar essa alteração em somente uma?
Neste artigo irei lhe ensinar esse truque!
Suponhamos que iremos substituir os valores da seguinte tabela trazendo seus respectivos nomes em português:
Usuários um pouco menos experientes, naturalmente, utilizam de mais de uma etapa para efetuar as substituições de valores na tabela. Utilizando o seguinte recurso:
Clicando com o botão direito do mouse no valor a ser alterado e clicando em “Substituir Valores...”:
Alterado o valor corrente, nesse caso, “Dog” por “Cachorro”, conforme abaixo:
Aplicando esse recurso quantas vezes sejam necessárias até substituir todos os valores, que nos retornará o seguinte resultado:
Realizar este procedimento resolve o nosso problema, porém, não resolve de maneira tão eficiente e performática. Utilizar o recurso “Substituir Valores...”, uma vez para cada substituição, irá gerar 5 novas etapas no Power Query. Sendo que cada um dessas etapas acaba reprocessando inteiramente toda a tabela da etapa anterior, gerando um gasto computacional muito alto, principalmente para uma tabela com muitas linhas.
Etapas criadas:
Código M e fluxo de tabelas reprocessadas nas etapas criadas até o resultado final:
A solução que eu trago para esse dilema pode ser replicada de maneira bem simples com um conhecimento da linguagem M não muito avançado.
O primeiro passo é utilizar a própria ajuda que o Power Query nos dá utilizando apenas cliques de mouse. Iremos recorrer à própria opção “Substituir Valores...” que foi abordada anteriormente.
Podemos aplicar a seguinte substituição para que ele nos dê o respectivo código que usaremos como base da nossa solução:
Código gerado pela etapa:
let
Fonte = Tabela,
#"Valor Substituído" = Table.ReplaceValue(
Fonte,
"Dog", // Valor a ser substituído
"Cachorro", // Novo valor
Replacer.ReplaceText,
{"Animal"} // Coluna onde será realizada a substituição
)
in
#"Valor Substituído"
Iremos alterar o seguinte código gerado utilizando algumas condicionais e o comando “each”, que, traduzido para o português, significa “cada”. Reaproveitando o código ilustrado acima, iremos substituir o segundo argumento (Valor a ser substituído) da função Table.ReplaceValue de “Dog” para each [Animal], que irá avaliar cada linha da coluna, verificando o seu valor.
Na sequência, iremos substituir o terceiro argumento (Novo valor) pela seguinte condicional:
if [Animal] = "Dog" then
"Cachorro"
else if [Animal] = "Cat" then
"Gato"
else if [Animal] = "Snake" then
"Cobra"
else if [Animal] = "Turtle" then
"Tartaruga"
else if [Animal] = "Goat" then
"Cabra"
else
""
E esse agrupamento de funções “if-else” irá verificar qual o valor em português deverá ser atribuído a seu respectivo par em inglês. Porém, essa verificação deverá ser precedida, também, pela função “each” para que seja avaliada linha a linha da coluna especificada, realizando, assim, a sua substituição de valor.
Como resultado, teremos o seguinte código, que será executado em somente uma etapa:
let
Fonte = Tabela,
#"Valor Substituído" = Table.ReplaceValue(
Fonte,
each [Animal], // Para cada linha da coluna "Animal"
each // Para cada linha executar a condicional abaixo
if [Animal] = "Dog" then
"Cachorro"
else if [Animal] = "Cat" then
"Gato"
else if [Animal] = "Snake" then
"Cobra"
else if [Animal] = "Turtle" then
"Tartaruga"
else if [Animal] = "Goat" then
"Cabra"
else
"",
Replacer.ReplaceText,
{"Animal"}
)
in
#"Valor Substituído"
Importante! Ao utilizar uma substituição de valores em massa, lembre-se de comentar o código na etapa atual, para facilitar o entendimento por parte de outros desenvolvedores que venham a utilizar o mesmo arquivo no futuro. Organização é algo essencial no desenvolvimento de qualquer projeto.
Logo abaixo apresento a comparação da performance entre as consultas, demonstrando o quanto é importante utilizar o mínimo de etapas sempre que possível.
Obs 1: Os testes de performance foram realizados utilizando a "Análise de Diagnóstico" nativo do Power Query.
Obs 2: Os valores apresentados no gráfico estão na escala de microsegundos. Os resultados foram multiplicados por 10^6 para facilitar a visualização, compreensão e entendimento a respeito da performance das consultas.
Obs 3: O resultado do teste apresentado acima pode variar de acordo com a configuração do computador onde foi realizado. O teste acima é resultado de um computador com processador Ryzen 5 3600 com 16gb de memória ram.
Conclusão:
As duas consultas são capazes de chegar ao mesmo resultado, porém, a consulta não otimizada irá utilizar 5 etapas para realizar o que a consulta ideal faria em apenas 1 etapa.
Espero que essa dica seja útil e possa ajuda-los em projetos futuros trazendo um pouco mais de performance e legibilidade ao código de vocês!
Grande abraço a todos e até a próxima dica! 🚀