Tabela dicionário para padronizar dados

Você já se deparou com aquela planilha bagunçada, onde os dados estão completamente despadronizados em uma coluna? Se sim, você sabe o quão frustrante pode ser lidar com essa situação. Mas não se preocupe, neste artigo, vou compartilhar uma dica valiosa que vai salvar o seu dia!

Hoje, vamos falar sobre como utilizar tabelas dicionário para realizar o tratamento de dados no Power BI, mais especificamente utilizando o Power Query e a linguagem M. Essa técnica é extremamente útil para lidar com situações comuns em que os dados estão mal formatados ou não padronizados.

Imagine que você está realizando uma análise de dados e precisa calcular o faturamento por cidade. Ao analisar os dados, você se depara com uma coluna de cidades onde os nomes estão escritos de formas diferentes: “São Paulo” sem acento, “SP”, “Sampa” e assim por diante. Esse é um cenário comum quando os dados não são inseridos de forma padronizada.

A abordagem ideal seria tratar os dados na fonte, evitando que situações como essa ocorressem. No entanto, nem sempre é possível realizar essa validação na entrada de dados. Nesses casos, o Power Query se torna uma ferramenta poderosa para o tratamento de dados.

Vamos pensar em como resolver esse problema utilizando o Power Query. Normalmente, você faria substituições manualmente para padronizar os valores. No entanto, isso se torna trabalhoso e inviável, especialmente quando há várias ocorrências diferentes para tratar.

É aqui que entram as tabelas dicionário. Se você nunca ouviu falar desse conceito, não se preocupe, eu vou explicar. Uma tabela dicionário é essencialmente uma tabela onde você correlaciona os valores que deseja substituir por outros. É como uma tabela de tradução, onde você especifica o que deve ser substituído por quê.

Vamos agora ver como podemos implementar as tabelas dicionário no Power BI para resolver nosso problema de padronização de cidades. O primeiro passo é importar a tabela dicionário, que é essencialmente uma lista de pares de valores que queremos substituir.

Para fazer isso, vamos abrir o Power Query e importar a tabela dicionário do Excel. Esta tabela contém uma lista dos valores que queremos substituir e pelo que queremos substituí-los.

Com a tabela dicionário importada, podemos então utilizar essa tabela como referência no Power Query para automatizar o processo de substituição dos valores não padronizados pela versão correta.

Utilizar tabelas dicionário no Power BI é uma estratégia eficaz para lidar com dados despadronizados e facilitar o processo de limpeza e tratamento de dados. Ao correlacionar os valores que desejamos substituir por outros, podemos automatizar esse processo e economizar tempo e esforço.

O primeiro passo é criar uma função personalizada que nos permitirá substituir os valores com base na tabela dicionário. Vamos iniciar este processo no Editor Avançado do Power Query.

Vamos criar uma consulta a partir de uma fonte nula e chamá-la de fnSubstituirValores.

No Editor Avançado, vamos começar a escrever a função. Primeiro, criaremos os parâmetros necessários para a função. Para simplificar, vamos criar uma lista simulada de valores que desejamos substituir e seus correspondentes. Por exemplo:

Em seguida, vamos combinar essas listas utilizando a função `List.Zip`, que retorna uma lista de listas combinando os itens na mesma posição.

Agora que temos nossas listas combinadas, podemos prosseguir com a substituição dos valores.

No Editor Avançado, utilizamos a função `List.ReplaceMatchingItems` para substituir os valores no texto conforme a nossa lista dicionário. Isso nos permitirá substituir todas as correspondências de uma só vez.

Com a estrutura básica da função pronta, a convertemos em uma função personalizada utilizando o operador `=>` e definindo os argumentos entre parênteses.

( Texto as text, De as list, Para as list )=>
let
    // De = {"Sampa", "RJ", "Sanca"},
    // Para = {"São Paulo", "Rio de Janeiro", "São Caetano do Sul"},
    // Texto = "RJ",
    Zip = List.Zip({ De, Para }),
    Resultado = List.ReplaceMatchingItems( { Texto }, Zip ){0}
in
    Resultado

Agora, temos uma função personalizada chamada `fnSubstituirValores` que podemos utilizar para realizar as substituições na nossa tabela de vendas.

Com a função criada, vamos agora aplicá-la na coluna da tabela de vendas que contém os dados de cidade. Para isso, seguiremos os seguintes passos:

Selecionaremos a tabela de vendas no Power BI.

Criaremos uma coluna chamada “Cidade Corrigida” para armazenar os valores corrigidos.

Utilizaremos uma coluna personalizada para aplicar a função `fnSubstituirValores` à coluna de cidade original.

Utilizaremos um truque para aplicar a transformação diretamente na coluna existente, sem a necessidade de criar uma nova.

Para realizar a transformação diretamente na coluna existente, utilizaremos a linguagem M e a função `Table.TransformColumns`. Este método nos permite aplicar a função de substituição a cada valor da coluna de cidade, garantindo que os valores sejam corrigidos de forma eficiente e sem a necessidade de criar uma coluna.

Primeiramente adicionamos uma nova etapa onde trazemos as colunas “De” e “Para” da tabela dicionário.

Criamos uma etapa chamada “CidadeCorrigida” onde utilizamos a função `Table.TransformColumns`.

Passamos como argumentos a tabela “TipoAlterado“, o nome da coluna a ser transformada (“Cidade”), e a função de transformação `fnSubstituirValores`.

Por fim, tipamos a coluna como texto para garantir a consistência dos dados.

Com esta abordagem, conseguimos realizar a transformação diretamente na coluna existente, economizando tempo e evitando a criação de uma nova coluna. Isso torna o processo mais eficiente e simplificado, garantindo que nossos dados estejam corrigidos e padronizados para futuras análises.

Após aplicar a função de substituição à coluna de cidade, percebemos que o carregamento da consulta estava demorando devido à necessidade de verificar cada cidade na tabela dicionário. Para melhorar a performance, utilizamos a função `List.Buffer` para armazenar as listas de substituição na memória, evitando recálculos desnecessários para cada linha da consulta.

Segue código completo:

let
    Fonte = Excel.Workbook(File.Contents("G:\Drives compartilhados\Conteúdo\02. YouTube\Vídeos\004. Tabela Dicionário\Arquivos\Vendas.xlsx"), null, true),
    Vendas_Table = Fonte{[Item="Vendas",Kind="Table"]}[Data],
    TipoAlterado = Table.TransformColumnTypes(Vendas_Table,{{"Pedido", Int64.Type}, {"Data.Pedido", type date}, {"Data.Entrega", type date}, {"Cod.Vendedor", Int64.Type}, {"Unidade", type text}, {"Cidade", type text}, {"Cod.Cliente", Int64.Type}, {"Produto", type text}, {"Qtde", Int64.Type}, {"Vl.Unitario", type number}, {"Vl.Frete", type number}}),
    De = List.Buffer(Dicionario[De]),
    Para = List.Buffer(Dicionario[Para]),
    CidadeCorrigida = Table.TransformColumns(TipoAlterado, { "Cidade", each fnSubstituirValores(_, De, Para), type text }),
    #"Linhas Filtradas" = Table.SelectRows(CidadeCorrigida, each true)
in
    #"Linhas Filtradas"

Com a consulta otimizada e as substituições realizadas de forma eficiente, concluímos o processo de tratamento de dados. Agora, nossa tabela de vendas está limpa, padronizada e pronta para análises futuras.

Espero que este tutorial tenha sido útil para você aprender a utilizar tabelas dicionário e linguagem M no Power BI para tratar dados despadronizados. Estas técnicas são essenciais para garantir a qualidade e confiabilidade das análises realizadas.