PROCV Aproximado no Power Query e no DAX

Olá, FERA! Hoje vamos desbravar o Procv Aproximado no Power Query do Excel e do Power BI, onde você vai aprender a fazer vínculos entre tabelas com granularidades diferentes.

Vamos lá, pegue seu café e sente-se confortavelmente, porque a aula vai começar!

Introdução ao Vínculo entre Tabelas

Você já se deparou com a necessidade de vincular uma tabela de faturamento a uma tabela de contratos, mas esbarrou na diferença de detalhamento entre elas?

Pois é, isso é mais comum do que parece.
Mas não se preocupe, porque hoje você vai aprender a solucionar esse problema como um verdadeiro mestre dos dados!

Entendendo a Granularidade das Tabelas

Vamos lá, FERA! Imagine que você tem um monte de contratos que funcionam como pacotes de serviços, cada um valendo por um período específico, representado por um intervalo de datas “de – até”. Agora, imagine que você tem uma pilha de faturas, cada uma com uma data específica em que o serviço foi prestado.

Agora, o desafio é o seguinte: você precisa descobrir a qual contrato cada fatura está relacionada, considerando a data em que o serviço foi prestado. É como se você tivesse que encontrar o par perfeito para cada meia solitária em uma gaveta cheia delas, onde cada par representa um contrato e cada meia solitária representa uma fatura.

Para fazer isso, você vai usar o poder do Power Query para criar uma relação entre as tabelas de contratos e faturas, usando o intervalo de datas “de – até” dos contratos para encontrar o contrato correspondente para cada fatura. É uma tarefa que pode parecer complexa, mas com as ferramentas certas, você vai ver que é mais simples do que parece!

Aqui, a granularidade das tabelas entra em jogo, pois enquanto a tabela de contratos tem uma visão mais ampla, representando períodos mais longos, a tabela de faturas tem uma visão mais detalhada, focada em dias específicos. Entender essa diferença de granularidade e saber como trabalhar com ela é o primeiro passo para se tornar um mestre no tratamento de dados!

Criando Colunas Calculadas

Agora que já entendemos a importância de organizar nossos dados, vamos criar colunas calculadas para determinar a faturação baseada nas datas dos contratos.

Dependendo de sua fonte de dados e da ferramenta você faria de uma forma diferente, mas hoje vou te mostrar como você pode fazer isso no Power Query (tanto do Excel quanto do Power BI).

Vamos criar uma coluna que vai nos mencionar o número do contrato vigente para aquela transação, e depois, quando você estiver no DAX (por exemplo), o relacionamento será possível.

Fazendo a busca do contrato por faixa de valores

E aí, FERA! Agora que já entendemos a granularidade das nossas tabelas, vamos mergulhar no Power Query para criar uma coluna totalmente nova que vai facilitar e muito a nossa vida. Vamos criar a coluna “ContratoVigente” na tabela “Vendas”. Essa coluna vai ser o nosso guia para saber qual contrato estava vigente no momento de cada venda.

Para fazer isso, vamos criar uma função que vai avaliar as colunas “DataVenda” e “ClienteID” na tabela “Vendas” e buscar na tabela “Contratos” as informações correspondentes, considerando as colunas “ClienteID”, “DataVigenciaDe” e “DataVigenciaAte”.

Se liga no comando exemplo:

let
    AdicionarColuna = Table.AddColumn(
        Vendas, 
        "ContratoVigente", 
        each 
            let
                DataVendaAtual = [DataVenda], 
                ClienteIDAtual = [ClienteID], 
                ContratoVigente = Table.SelectRows(
                    Contratos, 
                    each 
                        [ClienteID] = ClienteIDAtual 
                    and [DataVigenciaDe] <= DataVendaAtual 
                    and [DataVigenciaAte] >= DataVendaAtual
                )
            in
                // retornar a primeira linha, caso encontre mais que uma
                if Table.RowCount(ContratoVigente) > 0 then
                    Record.Field(ContratoVigente{0}, "ContratoID")
                else
                    null
    )
in
    AdicionarColuna

Essa é uma forma de fazer, que vai fazer uma avaliação à cada linha, retornando o primeiro valor identificado.

Por isso é bom lembrar, que assim como o PROCV aproximado, é importante que você classifique (ordene) primeiro seus dados da tabela “Contratos”.

Dica Bônus: Como fazer o PROCV aproximado no DAX?

vamos elevar o nível e usar as funções MINX e FILTER no DAX para encontrar o contrato vigente de cada venda. É como se estivéssemos usando um detector de metais superpotente para encontrar o tesouro escondido mais valioso!

Na tabela “Vendas”, crie uma coluna calculada, com esse comando:

ContratoVigente = 
VAR DataVendaAtual = Vendas[DataVenda]
VAR ClienteAtual = Vendas[ClienteID]
RETURN
    CALCULATE(
        MINX(
            FILTER(
                Contratos,
                   Contratos[ClienteID] = ClienteAtual 
                && Contratos[DataVigenciaDe] <= DataVendaAtual 
                && Contratos[DataVigenciaAte] >= DataVendaAtual
            ),
            Contratos[ContratoID]
        )
    )

Quando você estiver a fazer um cálculo desses, você tem vários caminhos de resolução, e cada um com uma velocidade diferente.

Mas saiba que a velocidade não está ligada só à linguagem, e sim, ao que você está aplicando antes e depois daquela função.

No caso do DAX, os dados já vieram tratados do Power Query, e aplicar uma função como essa, em dados que já estão no modelo, pode ser ligeiramente mais rápido que no Power Query, mas, isso porque ele está trabalhando com dados já tratados.

Entendeu a diferença? deixe nos comentários desse post sua conclusão!

Fixação com aula em vídeo

Esse resolução em Power Query está explicada detalhadamente em um vídeo no meu canal do Youtube, que você pode acessar clicando aqui.

Foram 18 minutos de uma aula completona sobre esse assunto, para que você possa replicar hoje mesmo.

Pontos de Atenção

Além da escolha do ponto onde aplicar a resolução desse problema, você tem também uma infinidade de conceitos aplicados que podem mudar seu ponto de vista, e fazer de uma forma ainda diferente, como mostrei na LIVE 025, onde aplicamos esse conceito resolvendo o caso de: “Ordem de Serviço x Ordem que Produziu o Produto” (com famoso De-Até).

Ou também no caso do Imposto de Renda, que também tem Faixa de Valores, o qual apresentamos na LIVE 020, criando um cálculo composto entre faixas de IRRF e INSS.

Ambas LIVES estão disponíveis com seus materiais de apoio no módulo LIVES da FORMAÇÃO POWER QUERY 2.0, que é um pacote com mais de 15 cursos e centenas de horas aplicadas ao Ecossistema Power Query > SQL > DAX > Python.

Torne-se um mestre no manuseio de dados, e descubra o poder que você nem imaginava que a ferramenta tinha.

Conclusão: O Poder do Tratamento de Dados

Parabéns, FERA! Você chegou ao fim desta jornada e agora possui o conhecimento necessário para transformar horas de trabalho tedioso em minutos de pura eficiência. Mas não pare por aqui! Convido você a conhecer a Formação Power Query 2.0, onde você vai aprender ainda mais sobre o universo do tratamento de dados e se tornar um verdadeiro ninja do Power Query e Power BI.

Forte abraço do