Transformar coluna/linhas mescladas com dados distintos em colunas separadas no Power Query

[Jonathas Gomes]

Olá pessoal.

Tenho uma planilha em excel com diversas pastas de trabalho (abas ou sheets), das quais preciso conectar e utilizar apenas duas, sendo CG 2020-2021 - ANALÍTICO e CG 2022 - ANALÍTICO, respectivamente.

Nestas abas, possuem históricos de pagamentos de fornecedor nos anos de 2020, 2021 e 2022.

Eu preciso importar esta planilha e utilizar estas duas abas, ocorre que, na planilha, fonte de dados, tenho uma linha mesclada que informa: o número da nota Fiscal (NF), o número do processo (Processo), o mês (OUTUBRO) e o ano (2022). Esta linha, para o usuário, serve para demonstrar de forma agrupada que naquele mês, ano, processo e nota fiscal, foram realizados pagamentos de diversos serviços, serviços estes que possuí QTDE, VLR e uma coluna calculada com o TOTAL daquele mês. (ver imagem Captura.PNG).

Ocorre que, no Power Query esta linha mesclada contém as 4 informações agrupadas na mesma coluna, tais informações eu preciso que sejam transformadas em colunas separadas. Mesmo transformando as linhas em colunas, não foi o suficiente para resolver o problemas, pois deixa o projeto menor performático (36 meses praticamente repetindo 3 colunas cada mês) e perde-se a referencia do contexto daqueles serviços com os dados de pagamento. (ver imagem Captura2.PNG, do Power Query).

Disponibilizo anexo, também, a planilha em excel (ver Servicos_XXX - Geral ANÁLISE.xlsx) para possam me ajudar a encontrar soluções para este caso, onde preciso que as 4 informações que constam na mesma coluna/linha, sejam transformadas cada uma em uma tabela, agrupando todos os meses.

Exemplo da resultado desejado: agrupar todas as notas fiscais na mesma coluna; agrupar todos os processos na mesma coluna...

__________________________________

NF  |  Processo  |  Mês  |   Ano   |

001 | 012/2020 |  12      | 2020  |

002 | 014/2021 |  01      | 2021  |

236 Z 695/2020 | 02       | 2020  |
------------------------------------------------------

Desde já agradeço.

Servicos_XXX - Geral ANÁLISE.xlsx

[Vitor Peralva]

Boa Noite, @Jonathas Gomes!

Dá uma olhada nesta sugestão de resolução.

Fiz para o "CG 2022 - ANALÍTICO", mas a linha é a mesma para o outro.

Não separei por mês, porque pode setar como data e diferenciar via dCalendario.

Tb exclui os totais, já que eles podem ser obtidos diretamente via DAX.

Basta alterar o caminho do arquivo na etapa de Fonte.

 

let
    Fonte = Excel.Workbook(File.Contents("C:\Users\vitor\Downloads\Servicos_XXX - Geral ANÁLISE.xlsx"), null, true),
    #"CG 2022 - ANALÍTICO_Sheet" = Fonte{[Item="CG 2022 - ANALÍTICO",Kind="Sheet"]}[Data],
    #"Linhas Filtradas" = Table.SelectRows(#"CG 2022 - ANALÍTICO_Sheet", each ([Column6] <> null)),
    #"Tabela Transposta" = Table.Transpose(#"Linhas Filtradas"),
    #"Preenchido Abaixo" = Table.FillDown(#"Tabela Transposta",{"Column1"}),
    #"Colunas Mescladas" = Table.CombineColumns(#"Preenchido Abaixo",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Mesclado"),
    #"Tabela Transposta1" = Table.Transpose(#"Colunas Mescladas"),
    #"Cabeçalhos Promovidos" = Table.PromoteHeaders(#"Tabela Transposta1", [PromoteAllScalars=true]),
    #"Outras Colunas Não Dinâmicas" = Table.UnpivotOtherColumns(#"Cabeçalhos Promovidos", {" - Código", " - Categoria", " - Serviço", " - UNIDADE", " - VALOR UNIT."}, "Atributo", "Valor"),
    #"Dividir Coluna por Delimitador" = Table.SplitColumn(#"Outras Colunas Não Dinâmicas", "Atributo", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"NF", "PROCESSO", "REFERÊNCIA", "ATRIBUTO"}),
    TransformaColunas = 
        Table.TransformColumns(
            #"Dividir Coluna por Delimitador",
            {
                {"NF", each Text.Select(_, {"0" .. "9"}), type text},
                {"ATRIBUTO", each Text.Select(_, {"A" .. "Z"}), type text}
            }
    ),
    #"Linhas Filtradas1" = Table.SelectRows(TransformaColunas, each ([ATRIBUTO] = "QTDE" or [ATRIBUTO] = "VLR")),
    #"Coluna em pivô" = Table.Pivot(#"Linhas Filtradas1", List.Distinct(#"Linhas Filtradas1"[ATRIBUTO]), "ATRIBUTO", "Valor")
in
    #"Coluna em pivô"

 

[Jonathas Gomes]

Consegui resolver o problema com sua proposta de solução. MUITO obrigado.

A solução se aplicou em ambas as planilhas.

Embora eu esteja imensamente grato pela solução que você desenvolveu, ainda tenho uma questão a resolver neste case, que é Unificar as abas da mesma planilhas na mesma tabela.

Como pode-se observar, em uma planilha tenha os pagamentos de 2020 e 2021 e em outra planilha de 2022, como combinar ou unificar na mesma tabela utilizando o Power Query?

[Vitor Peralva]

31 minutos atrás, Jonathas Gomes disse:

Consegui resolver o problema com sua proposta de solução. MUITO obrigado.

A solução se aplicou em ambas as planilhas.

Embora eu esteja imensamente grato pela solução que você desenvolveu, ainda tenho uma questão a resolver neste case, que é Unificar as abas da mesma planilhas na mesma tabela.

Como pode-se observar, em uma planilha tenha os pagamentos de 2020 e 2021 e em outra planilha de 2022, como combinar ou unificar na mesma tabela utilizando o Power Query?

 

Existem diversas formas de fazer isto.
Uma delas, talvez a mais simples, seria:

1) Com um dos arquivos tratados, Vá na Guia Página Inicial, dentro do Grupo Combinar, escolha Acrescentar Consultas.

2) Por padrão, ele já vem com Duas Tabelas selecionado, aí basta escolher na lista de tabelas, aquela que quer acrescentar.

3) Depois, clique com o botão direito na consulta que acrescentou e desmarque o "Habilitar Carga", pois o conteúdo dela já vai estar dentro da outra que combinou os arquivos.

Poderia se fazer um trabalho com função, também, mas o ganho de performance seria pequeno, frente a necessidade de novos conceitos que teria que absorver.

 

Espero que ajude.