Cruzamento Avançado Entre 2 Tabelas

[14alex25]

Olá, comunidade!

Estamos com um desafio de saber, a partir de uma relação de clientes contatados via chamada telefônica, cruzar com uma tabela de vendas para medir conversão de vendas dos atendentes.

Nós temos duas tabelas:

ft_contato  que lista todas as ligações realizadas para uma base de clientes:

ft_vendas  que é uma tabela de vendas comum: 

Nossa regra de negócio é que o atendente que contatar um cliente e este realizar uma compra em até 30 dias da ligação, esta venda conta para ele.

Para realizar esta contabilização, levamos em consideração tanto o Telefone quanto o CPF do cliente.

Então, caso o Telefone da ft_contato seja localizado na ft_vendas e esteja dentro do intervalo de data de 30 dias, então esta venda é contabilizada para o atendente, 

da mesma forma que, caso o CPF do cliente seja localizado na ft_vendas e esteja dentro do intervalo de data de 30 dias, então esta venda também é contabilizada para o atendente.

Podem ocorrer casos em que o Telefone e/ou o CPF do cliente não sejam os mesmos ao comparar uma tabela com a outra, por vários motivos (um deles é o cliente ter alterado a titularidade do Telefone, ou outros motivos). Por isso não dá para realizar uma Mescla com 2 parâmetros (utilizando CPF e Telefone juntos como chave)

 

Gerando, ou não, uma terceira tabela resultante:

 

Também existiria a possibilidade de buscar dados da venda para a tabela de contato, utilizando a linguagem M, mas não conseguimos.

Exemplo Resultado esperado Cruzamento Linguagem M.xlsx

Editado Maio 11 por 14alex25

[Vitor Peralva]

Boa Noite, @14alex25!

 

Tente a utilização do seguinte código alterando a fonte:

let
    Fonte = Excel.Workbook(File.Contents("C:\Users\vitor\Downloads\Exemplo Resultado esperado Cruzamento Linguagem M.xlsx"), null, true),
    ft_vendas_Sheet = Fonte{[Item="ft_vendas",Kind="Sheet"]}[Data],
    #"Cabeçalhos Promovidos" = Table.PromoteHeaders(ft_vendas_Sheet, [PromoteAllScalars=true]),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Cabeçalhos Promovidos",{{"pedido", type text}, {"telefone", Int64.Type}, {"cpfCliente", Int64.Type}, {"dataVenda", type date}, {"grupoEstoque", type text}, {"valor", Int64.Type}, {"vendedor", type text}}),
    #"Consultas Mescladas" = Table.NestedJoin(#"Tipo Alterado", {"telefone"}, ft_contato, {"telefone"}, "Tel", JoinKind.LeftOuter),
    #"Consultas Mescladas1" = Table.NestedJoin(#"Consultas Mescladas", {"cpfCliente"}, ft_contato, {"cpfCliente"}, "CPF", JoinKind.LeftOuter),
    #"Personalização Adicionada" = Table.AddColumn(#"Consultas Mescladas1", "Personalizar", each Table.Distinct(Table.Combine({[Tel], [CPF]}))),
    #"Colunas Removidas" = Table.RemoveColumns(#"Personalização Adicionada",{"Tel", "CPF"}),
    #"Personalizar Expandido" = Table.ExpandTableColumn(#"Colunas Removidas", "Personalizar", {"nomeCliente", "dataContato", "atendente"}, {"nomeCliente", "dataContato", "atendente"}),
    #"Linhas Filtradas" = Table.SelectRows(#"Personalizar Expandido", each ([dataContato] >= Date.AddDays([dataVenda], -30))),
    #"Personalização Adicionada1" = Table.AddColumn(#"Linhas Filtradas", "diferença de datas", each Duration.Days([dataVenda] - [dataContato]), Int64.Type)
in
    #"Personalização Adicionada1"

 

Espero que ajude.