As funções iteradoras do Power BI são uma poderosa ferramenta para análise de dados, executando uma determinada operação repetidamente em uma tabela, linha por linha, para retornar um resultado agregado ou, até mesmo, um filtro em tabela. Neste artigo, discutiremos as principais funções iteradoras do Power BI, suas características e como utilizá-las para realizar cálculos mais avançados. Antes de prosseguir, é importante ressaltar que o uso dessas funções deve ser feito com cautela, uma vez que pode aumentar significativamente o tempo de processamento da análise. Portanto, é importante avaliar se a utilização dessas funções é realmente necessária e buscar otimizar o modelo de dados para que o cálculo seja realizado da maneira mais eficiente possível. Dito isto, iremos utilizar a seguinte tabela, como exemplo, para o entendimento das funções: Com isso em mente, vamos às funções iteradoras! 1. FILTER Já iremos começar esse artigo quebrando um paradigma! Ao contrário do que muitos pensam, as funções iteradoras não são somente aquelas funções terminadas em "X". A função FILTER, apesar de não terminar com a letra "X", é uma função iteradora que permite filtrar um conjunto de dados baseado em uma condição. A condição especificada irá iterar linha a linha da tabela verificando se atende a esse critério determinado, que, nesse caso, será filtrar o Produto A. É possível utilizar essa tabela filtrada para criar medidas que dependem desse subconjunto de dados específico. Por exemplo, podemos criar uma medida que calcule a soma das vendas para esse produto em particular. Porém, nesse caso, iremos apenas filtrar a tabela para mostrar a funcionalidade da função FILTER. Sintaxe: FILTER( Tabela , Filtro ) Parâmetros: Tabela: A tabela a ser filtrada. A tabela também pode ser uma expressão que resulta em uma tabela. Expressão: Uma expressão booliana a ser avaliada para cada linha da tabela. Obs: Uma expressão booleana é uma expressão que avalia um valor do tipo de dados "Booleano": True ou False. Exemplo: FILTER( fVendas, fVendas[Produto] = "Produto A" ) Resultado da FILTER: 2. SUMX A função SUMX calcula o valor de uma expressão para cada linha de uma tabela e retorna a soma de todas as linhas. Sintaxe: SUMX( Tabela , Expressão ) Parâmetros: Tabela: A tabela que contém as linhas para as quais a expressão será avaliada. Expressão: A expressão a ser avaliada para cada linha da tabela. Exemplo: SUMX( fVendas, (fVendas[Preço Unitário] - fVendas[Desconto Unitário]) * fVendas[Quantidade] ) De forma visual, o que acontece internamente é o seguinte: O Power BI, internamente, "cria" um a coluna virtual "X" onde armazena o valor do resultado da expressão especificada para cada linha e, ao final do processo, realiza a soma dos valores. Resultado da SUMX: R$ 6.743,47 Visualização em um cartão: 3. AVERAGEX De forma análoga a função SUMX, a função AVERAGEX calcula o valor de uma expressão para cada linha de uma tabela e retorna a média de todas as linhas através do mesmo processo, utilizando a coluna "X" apresentada anteriormente. Sintaxe: AVERAGEX( Tabela , Expressão ) Parâmetros: Tabela: Nome de uma tabela ou uma expressão que especifica a tabela sobre a qual a agregação pode ser executada. Expressão: Uma expressão com um resultado escalar, que será avaliado para cada linha da tabela no primeiro argumento. Exemplo: AVERAGEX( fVendas, (fVendas[Preço Unitário] - fVendas[Desconto Unitário]) * fVendas[Quantidade] ) Resultado da AVERAGEX: R$ 354,92 Visualização em um cartão: 4. MINX e MAXX As funções MINX e MAXX permitem encontrar o menor e o maior valor em um conjunto de dados, respectivamente. É possível utilizar essas funções para criar medidas que encontram o menor ou o maior valor em um conjunto ou subconjunto de dados específico. Sintaxe: MINX( Tabela , Expressão ) MAXX( Tabela , Expressão ) Parâmetros: Tabela: A tabela que contém as linhas para as quais a expressão será avaliada. Expressão: A expressão a ser avaliada para cada linha da tabela. Exemplo: MINX( fVendas, (fVendas[Preço Unitário] - fVendas[Desconto Unitário]) * fVendas[Quantidade] ) MAXX( fVendas, (fVendas[Preço Unitário] - fVendas[Desconto Unitário]) * fVendas[Quantidade] ) De forma visual, as funções MINX e MAXX, baseados na expressão definida em seu segundo argumento, conseguirão encontrar o menor e maior valor para a tabela especificada: Resultado da MINX: R$ 13,97 Visualização em um cartão: Resultado da MAXX: R$ 1.566,57 Visualização em um cartão: 5. COUNTX A função COUNTX retorna o número de linhas não vazias em uma coluna ou o número de resultados não vazios da expressão especificada em um conjunto de dados. Sintaxe: COUNTX( Tabela , Expressão ) Parâmetros: Tabela: A tabela que contém as linhas a serem contadas. Expressão: Uma expressão que retorna o conjunto de valores que contém os valores que você deseja contar. Exemplo: COUNTX( fVendas, (fVendas[Preço Unitário] - fVendas[Desconto Unitário]) * fVendas[Quantidade] ) De forma visual: Resultado da COUNTX: 19 (contagem de linhas não vazias) Visualização em um cartão: 6. CONCATENATEX A função CONCATENATEX permite concatenar valores de um conjunto de dados. É possível utilizar essa função para criar medidas que concatenam valores de um conjunto ou subconjunto de dados específico. Por exemplo, podemos criar uma medida que concatena os nomes de produtos vendidos. Sintaxe: CONCATENATEX( Tabela , Expressão , Delimitador , Expressão de Ordenação , Ordenação ) Parâmetros: Tabela: A tabela que contém as linhas a serem contadas. Expressão: Uma expressão que retorna o conjunto de valores que contém os valores que você deseja contar. Delimitador: (Opcional) Um separador a ser usado durante a concatenação. Expressão de Ordenação: (Opcional) Qualquer expressão DAX em que o valor de resultado é usado para classificar os valores concatenados na cadeia de caracteres de saída. A avaliação é realizada para cada linha da "Tabela". Ordenação: (Opcional) Um valor que especifica como classificar valores "Expressão de Ordenação" em ordem crescente ou decrescente. Exemplo 1: CONCATENATEX( fVendas, // Tabela fVendas[Produto], // Expressão: Valor da coluna "Produto" ", " // Delimitador entre as ocorrências da coluna "X" ) De forma visual: Resultado: Produto E, Produto E, Produto E, Produto E, Produto E, Produto E, Produto E, Produto E, Produto C, Produto C, Produto C, Produto A, Produto A, Produto A, Produto A, Produto D, Produto F, Produto B, Produto B Visualização em um cartão: No caso da CONCATENATEX o resultado pode parecer um pouco mais confuso ou não intuitivo. Porém está sendo verificado para cada linha, da tabela de vendas, o nome do produto (expressão aplicada no segundo argumento) e concatenando com os outros valores presentes na "coluna X" criada virtualmente. No entanto, não há ordenação, porque omitimos o 4º argumento, que é opcional. Exemplo 2: Tabela: dVendedores (3 linhas) CONCATENATEX( dVendedores, // Tabela dVendedores[Nome] & " " & dVendedores[Sobrenome], // Nome Completo ", " // Delimitador ) De forma visual: Resultado: Caio Oliveira, Maria Silva, Lucas Rodrigues Visualização em um cartão: IMPORTANTE! Note que as funções iteradoras utilizam de uma tabela em seu primeiro argumento, entretanto, não é necessário que utilizemos tabelas que estejam materializadas em nosso modelo. Podemos recorrer a funções que retornam tabelas para realizar cálculos ainda mais personalizados. Tabela: VALUES(fVendas[Produto]) Neste exemplo retornaremos a lista de produtos vendidos, sem repetição (porque não há necessidade) e ordenados alfabeticamente. CONCATENATEX( VALUES(fVendas[Produto]), // Retorna uma tabela com os valores distintos do campo utilizado fVendas[Produto], ", ", fVendas[Produto] // Ordenando pelo produto (ordem alfabética) ) Resultado: Produto A, Produto B, Produto C, Produto D, Produto E, Produto F (Os 6 produtos presentes na fVendas) Visualização em um cartão: De forma semelhante, podemos utilizar todas a funções iteradoras apresentadas anteriormente associadas a VALUES(fVendas[Produto]) e obter o seguinte resultado: As colunas evidenciam os resultados das medidas, criadas anteriormente, com cada uma das funções apresentadas nesse artigo para cada um dos produtos vendidos na tabela fVendas. Espero que esse artigo possa ajudá-los de alguma forma no entendimento e aplicação das funções e me encontro à disposição para quaisquer dúvidas que possam surgir após a leitura. Caso necessitem do PBIX para um melhor entendimento do artigo, podem solicitar através dos comentários. Grande abraço a todos e até a próxima dica! 🚀

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! 🚀