Relatório Extraído de Vendas

[José Cendrette]

Olá bom dia, 

alguém poderia me dar um norte como posso deixar esse relatório de vendas com os seguintes campos finais

Produto/Serviço    Data    Qtde    ValorUnit    codVendas   Desconto    Cliente

Anexo segue planilha de exemplo.

 

 

 

vendas.xlsx

[Vitor Peralva]

Com base no que passou, acredito que poderia trabalhar com o seguinte código M:

 

let
    Fonte = Excel.Workbook(File.Contents("D:\Downloads\vendas (José Cendrette).xlsx"), null, true),
    vendas_Sheet = Fonte{[Item="vendas",Kind="Sheet"]}[Data],
    #"Linhas Principais Removidas" = Table.Skip(vendas_Sheet,5),
    #"Personalização Adicionada" = Table.AddColumn(#"Linhas Principais Removidas", "codVendas", each if Value.Is([Column1], Int64.Type) then [Column1] else null),
    #"Preenchido Abaixo" = Table.FillDown(#"Personalização Adicionada",{"codVendas"}),
    #"Linhas Agrupadas" = Table.Group(#"Preenchido Abaixo", {"codVendas"}, {{"Contagem", each _, type table [Column1=any, Column2=any, Column3=any, Column4=any, Column5=any, codVendas=number]}}),
    #"Personalização Adicionada1" = Table.AddColumn(#"Linhas Agrupadas", "TabelaTratada", each fxTratamento([Contagem])),
    #"Outras Colunas Removidas" = Table.SelectColumns(#"Personalização Adicionada1",{"TabelaTratada"}),
    #"TabelaTratada Expandido" = Table.ExpandTableColumn(#"Outras Colunas Removidas", "TabelaTratada", {"Column1", "Data", "Column4", "Column5", "codVendas", "Total Desconto", "Cliente"}, {"Column1", "Data", "Column4", "Column5", "codVendas", "Total Desconto", "Cliente"}),
    #"Colunas Renomeadas" = Table.RenameColumns(#"TabelaTratada Expandido",{{"Column1", "Produto/Serviço"}, {"Column4", "Qtde"}, {"Column5", "ValorUnit"}}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Colunas Renomeadas",{{"Produto/Serviço", type text}, {"Data", type date}, {"Qtde", Int64.Type}, {"ValorUnit", type number}, {"codVendas", Int64.Type}, {"Total Desconto", type number}, {"Cliente", type text}})
in
    #"Tipo Alterado"

 

Neste código de cima, alterar o caminho do arquivo.

 

A função utilizada é a seguinte:

(Tabela as table) =>

let
    
    TabelaBase = Tabela,
    #"Personalização Adicionada1" = Table.AddColumn(TabelaBase, "Data", each TabelaBase[Column1]{1}),
    #"Personalização Adicionada2" = Table.AddColumn(#"Personalização Adicionada1", "Cliente", each TabelaBase[Column1]{2}),
    #"Personalização Adicionada3" = Table.AddColumn(#"Personalização Adicionada2", "Desconto", each if [Column3] = "Desconto" then [Column5] else null),
    #"Personalização Adicionada4" = Table.AddColumn(#"Personalização Adicionada3", "Total", each if [Column3] = "Total" then [Column5] else null),
    #"Preenchido Acima" = Table.FillUp(#"Personalização Adicionada4",{"Desconto", "Total"}),
    #"Linhas Principais Removidas1" = Table.Skip(#"Preenchido Acima",6),
    #"Linhas Filtradas" = Table.SelectRows(#"Linhas Principais Removidas1", each ([Column4] <> null)),
    #"Personalização Adicionada5" = Table.AddColumn(#"Linhas Filtradas", "Total Desconto", each [Desconto]/[Total]),
    #"Outras Colunas Removidas" = Table.SelectColumns(#"Personalização Adicionada5",{"Column1", "Data", "Column4", "Column5", "codVendas", "Total Desconto", "Cliente"})
in
    #"Outras Colunas Removidas"

 

Com esta aplicação, acredito que obtém o resultado esperado:

[Vitor Peralva]

Boa Tarde, José Cendrette!

 

A sua planilha de correto acabou não sendo um bom exemplo, porque não traz dados da planilha de vendas, mas, provavelmente de outro mês, dificultando testar as informações.

Numa rápida olhada, não ficou claro para mim de onde se extrai a informação de codVendas, bem como se o Total Desconto seria o percentual de desconto aplicado.

Poderia esclarecer este pontos?

[José Cendrette]

8 horas atrás, fernando0877 disse:

O bom seria verificar se não há a possibilidade do sistema  gerar um relatório mais organizado.
Se só tem essa possibilidade, dá pra resolver com uma programação VBA em excel. Entendendo quais campos exatamente quer se extrair, e verificando extamente os "padrões da bagunça". Então vai descendo linha a linha, lendo a informação, verificando se naquela linha tem a informação que data, cliente, etc... e montando a tabela correta noutra planilha.

Por exemplo o valor desconto está 1 célula a direita do campo desconto. 

Mas francamente  não entendi de onde vieram os valores que formaram a panilha CORRETO. Onde termina um cliente e começa outro?

 

 

Boa noite Fernando, 

Obrigado pela indicação do VBA, a planilha CORRETO que enviei de referência se trata de um relatório que extrai do sistema antigo que eu tinha, mas já está com os campos corretos porque eu arrumei no powerquery.

a informação dos clientes estão nas linhas 8, 24, 40, 58 ou seja, não segue um padrão de quantidade de linhas.

Essa extração foi feito manualmente dentro do sistema copiando do cabeçalho até o final do arquivo e colando num arquivo Excel. Por isso essa bagunça. Ao meu ver terei que achar outro sistema no mercado ou desenvolver meu próprio e conseguir ter acesso direto ao banco de dados. Esse é o problema que tenho ao contratar um sistema de gestão no mercado que não permite ter acesso nem via API ao banco de dados.

Mesmo assim obrigado por ter respondido Fernando. Abraço.

[Vitor Peralva]

2 horas atrás, fernando0877 disse:

Vitor Peralva, peguei o embalo pra aprender. Não sabia que dava pra resolver isso. tenho uns problemas bem parecidos que eu já estava acostumado a resolver com VBA,. Por isso dei pitaco.
E desculpe se me intrometo na pergunta, José. Mas como se trata de um fórum, acho que é permitido moralmente 😁

Colando o seu código numa consulta nula, alterando o caminho do arquivo excel dá erro na linha "Personalização Adicionada 1"
Expression.Error: O nome 'fxTratamento' não foi reconhecido. Certifique-se de que ele foi soletrado corretamente.

Talvez seja porque não sei o que fazer com o código M seguinte que te referiste como "da função utilizada". Testei colar numa nova consulta nula, mas creio que não é assim. Como faz?

 

Olá, @fernando0877!

Aqui é um fórum de discussão, então, pitacos são sempre bem vindos, para podermos construir soluções juntos e crescermos em conjunto.

O VBA, ainda, é uma ferramenta que consegue nos auxiliar muito na resolução de problemas.

O Prof. Joviano Silveira, inclusive, costuma dizer que a melhor solução é aquela que você sabe fazer.

Sobre o erro que é apresentado, você precisa criar uma segunda consulta nula e colar o segundo código.

Ele vai gerar uma função (vai ficar com aquele Fx do lado esquerdo) e esta função vai fazer as transformações nas tabelas agrupadas.

Faz um teste e se tiver algum erro, informa aqui, que podemos tentar resolver juntos.

[fernando0877]

O bom seria verificar se não há a possibilidade do sistema  gerar um relatório mais organizado.
Se só tem essa possibilidade, dá pra resolver com uma programação VBA em excel. Entendendo quais campos exatamente quer se extrair, e verificando extamente os "padrões da bagunça". Então vai descendo linha a linha, lendo a informação, verificando se naquela linha tem a informação que data, cliente, etc... e montando a tabela correta noutra planilha.

Por exemplo o valor desconto está 1 célula a direita do campo desconto. 

Mas francamente  não entendi de onde vieram os valores que formaram a panilha CORRETO. Onde termina um cliente e começa outro?

 

 

Editado Março 14, 2022 por fernando0877

[José Cendrette]

8 horas atrás, fernando0877 disse:

O bom seria verificar se não há a possibilidade do sistema  gerar um relatório mais organizado.
Se só tem essa possibilidade, dá pra resolver com uma programação VBA em excel. Entendendo quais campos exatamente quer se extrair, e verificando extamente os "padrões da bagunça". Então vai descendo linha a linha, lendo a informação, verificando se naquela linha tem a informação que data, cliente, etc... e montando a tabela correta noutra planilha.

Por exemplo o valor desconto está 1 célula a direita do campo desconto. 

Mas francamente  não entendi de onde vieram os valores que formaram a panilha CORRETO. Onde termina um cliente e começa outro?

 

 

 

6 horas atrás, Vitor Peralva disse:

Boa Tarde, José Cendrette!

 

A sua planilha de correto acabou não sendo um bom exemplo, porque não traz dados da planilha de vendas, mas, provavelmente de outro mês, dificultando testar as informações.

Numa rápida olhada, não ficou claro para mim de onde se extrai a informação de codVendas, bem como se o Total Desconto seria o percentual de desconto aplicado.

Poderia esclarecer este pontos?

Boa noite Vitor, 

Agradeço pelas informações, vou tentar explicar, os campos da Planilha Correto são de uma base que já tenho de outro sistema, ele está organizado porque já tratei ele no PowerBI. 

Agora essa extração de vendas está vindo de outro sistema que eu puxo mensal copiando e colando no arquivo .xlsx, essa bagunça é por conta de que preciso dos dados do serviço e quantidade que nos relatórios exportados não tenho. O codVendas seria igual o campo Pedido que no começo do arquivo está finalizando em 98 e o Total Desconto está em percentual.

Grato.

[fernando0877]

Vitor Peralva, peguei o embalo pra aprender. Não sabia que dava pra resolver isso. tenho uns problemas bem parecidos que eu já estava acostumado a resolver com VBA,. Por isso dei pitaco.
E desculpe se me intrometo na pergunta, José. Mas como se trata de um fórum, acho que é permitido moralmente 😁

Colando o seu código numa consulta nula, alterando o caminho do arquivo excel dá erro na linha "Personalização Adicionada 1"
Expression.Error: O nome 'fxTratamento' não foi reconhecido. Certifique-se de que ele foi soletrado corretamente.

Talvez seja porque não sei o que fazer com o código M seguinte que te referiste como "da função utilizada". Testei colar numa nova consulta nula, mas creio que não é assim. Como faz?

[fernando0877]

@Vitor Peralva Deu certo, o que faltava pra mim era renomear a função criada (fx) pra "fxTratamento" daí a primeira consulta buscou pelo nome correto sem erros. Parabéns!!
Agora vou tirar um tempo pra interpretar essa função. Não era pra mim, mas já te agradeço.

Editado Março 15, 2022 por fernando0877
Melhorar concordância

[José Cendrette]

14 horas atrás, Vitor Peralva disse:

Com base no que passou, acredito que poderia trabalhar com o seguinte código M:

 

let
    Fonte = Excel.Workbook(File.Contents("D:\Downloads\vendas (José Cendrette).xlsx"), null, true),
    vendas_Sheet = Fonte{[Item="vendas",Kind="Sheet"]}[Data],
    #"Linhas Principais Removidas" = Table.Skip(vendas_Sheet,5),
    #"Personalização Adicionada" = Table.AddColumn(#"Linhas Principais Removidas", "codVendas", each if Value.Is([Column1], Int64.Type) then [Column1] else null),
    #"Preenchido Abaixo" = Table.FillDown(#"Personalização Adicionada",{"codVendas"}),
    #"Linhas Agrupadas" = Table.Group(#"Preenchido Abaixo", {"codVendas"}, {{"Contagem", each _, type table [Column1=any, Column2=any, Column3=any, Column4=any, Column5=any, codVendas=number]}}),
    #"Personalização Adicionada1" = Table.AddColumn(#"Linhas Agrupadas", "TabelaTratada", each fxTratamento([Contagem])),
    #"Outras Colunas Removidas" = Table.SelectColumns(#"Personalização Adicionada1",{"TabelaTratada"}),
    #"TabelaTratada Expandido" = Table.ExpandTableColumn(#"Outras Colunas Removidas", "TabelaTratada", {"Column1", "Data", "Column4", "Column5", "codVendas", "Total Desconto", "Cliente"}, {"Column1", "Data", "Column4", "Column5", "codVendas", "Total Desconto", "Cliente"}),
    #"Colunas Renomeadas" = Table.RenameColumns(#"TabelaTratada Expandido",{{"Column1", "Produto/Serviço"}, {"Column4", "Qtde"}, {"Column5", "ValorUnit"}}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Colunas Renomeadas",{{"Produto/Serviço", type text}, {"Data", type date}, {"Qtde", Int64.Type}, {"ValorUnit", type number}, {"codVendas", Int64.Type}, {"Total Desconto", type number}, {"Cliente", type text}})
in
    #"Tipo Alterado"

 

Neste código de cima, alterar o caminho do arquivo.

 

A função utilizada é a seguinte:

(Tabela as table) =>

let
    
    TabelaBase = Tabela,
    #"Personalização Adicionada1" = Table.AddColumn(TabelaBase, "Data", each TabelaBase[Column1]{1}),
    #"Personalização Adicionada2" = Table.AddColumn(#"Personalização Adicionada1", "Cliente", each TabelaBase[Column1]{2}),
    #"Personalização Adicionada3" = Table.AddColumn(#"Personalização Adicionada2", "Desconto", each if [Column3] = "Desconto" then [Column5] else null),
    #"Personalização Adicionada4" = Table.AddColumn(#"Personalização Adicionada3", "Total", each if [Column3] = "Total" then [Column5] else null),
    #"Preenchido Acima" = Table.FillUp(#"Personalização Adicionada4",{"Desconto", "Total"}),
    #"Linhas Principais Removidas1" = Table.Skip(#"Preenchido Acima",6),
    #"Linhas Filtradas" = Table.SelectRows(#"Linhas Principais Removidas1", each ([Column4] <> null)),
    #"Personalização Adicionada5" = Table.AddColumn(#"Linhas Filtradas", "Total Desconto", each [Desconto]/[Total]),
    #"Outras Colunas Removidas" = Table.SelectColumns(#"Personalização Adicionada5",{"Column1", "Data", "Column4", "Column5", "codVendas", "Total Desconto", "Cliente"})
in
    #"Outras Colunas Removidas"

 

Com esta aplicação, acredito que obtém o resultado esperado:

Boa tarde Vitor, 

Agradeço o retorno, gostei muito da função que usou para solucionar o caso, mas vi que o ValorUnit é o valor total do pedido, então quando eu faço a soma no DAX ele dobra de valor. Não consegui achar na sua função onde eu posso pegar o valor unitário que na verdade está na Column3.

grato meu amigo.

[José Cendrette]

6 horas atrás, fernando0877 disse:

Vitor Peralva, peguei o embalo pra aprender. Não sabia que dava pra resolver isso. tenho uns problemas bem parecidos que eu já estava acostumado a resolver com VBA,. Por isso dei pitaco.
E desculpe se me intrometo na pergunta, José. Mas como se trata de um fórum, acho que é permitido moralmente 😁

Colando o seu código numa consulta nula, alterando o caminho do arquivo excel dá erro na linha "Personalização Adicionada 1"
Expression.Error: O nome 'fxTratamento' não foi reconhecido. Certifique-se de que ele foi soletrado corretamente.

Talvez seja porque não sei o que fazer com o código M seguinte que te referiste como "da função utilizada". Testei colar numa nova consulta nula, mas creio que não é assim. Como faz?

Olá Fernando, 

Tamo junto meu amigo, o Vitor ajudou a ambos uma planilha que estava a tempos para conseguir tratar no PowerQuery de uma maneira que pegasse os campos corretos.

Estou na mesma tentando interpretar a função fxTratamento, no canal da Curbal tem alguns vídeos explicando como fazer para pegar valores de tipos diferentes na mesma coluna.
Dá uma olhada pode ser que consiga tirar mais insights lá também.

 

Abraço