Modelagem de dados no Power Query (Planilha com várias guias, transposição e transformar colunas em linhas)

[Gabriel Fernandes Pereira]

Boa noite, incomparáveis! 
Todos bem?

Pessoal, preciso da ajuda de vocês em relação a esse exemplo de planilha. Uma planilha que têm várias guias de excel, mês mesclado e colunas dividindo em REAL e ORÇADO.
Para esse case, as planilhas serão atualizadas e salvas em pasta com nome referenciado o ano. Para essa transformação precisaria das seguintes informações em coluna: descrição, mês, valor (real e orçado) e ano
Conseui caminhar até certo ponto com essas essas transformações, mas quando preciso transpor para fazer essas transformações não consigo dar solução.

Conseguem me ajudar?

Obrigado, pessoal!





 

Exemplo para fórum.xls

Editado Junho 9, 2023 por Gabriel Fernandes Pereira

[Vitor Peralva]

Bom Dia, @Gabriel Fernandes Pereira!

 

Fiz um tratamento fazendo as seguints presunções:

a) Os totais serão calculados via DAX, sendo desnecessários para fins da consulta final;

b) Cada Planilha de sua Pasta de Trabalho representa uma filial.

 

Crie uma consulta nula e cole o seguinte código:

 

let
    Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVBLasNADL2KGCgkkNIzNAmBQENKCt2YLBRbBFHPKB1pfKEuuuqyJ/DFOi64jEm6e+h9pKeqcgeqiQ1hGZOhW7gX8eiOi8rN4B7msPUXURMtmdHyxO+p/2oG14E0tYaN/ApWabDA1XxNeiFFhTVHMpyE/nENwSuFJiOCHcY3Mg7nm8paPDyTqmB7k39sPAdWi2jc/bNtwwFDrhMzfoBlxtOu29CRGnsKJuV8n2ywjDnT7E7a1H/231S0KRW51pn8wNUSLPIpcZZ/yNXDdiuY3c1B5RQJNmgp4nhJITz+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Descricao = _t, Tipo = _t]),
    #"Tipo Alterado" = Table.Buffer(Table.TransformColumnTypes(Fonte,{{"Descricao", type text}, {"Tipo", type text}}))
in
    #"Tipo Alterado"

 

Em uma nova consulta, cole o seguinte:

 

(Tabela as table) =>

let


    #"Colunas Removidas" = Table.RemoveColumns(Tabela,{"Column26", "Column27", "Column28"}),
    Base = #"Colunas Removidas",
    #"Primeiras Linhas Mantidas" = Table.FirstN(Base,2),
    #"Tabela Transposta" = Table.Transpose(#"Primeiras Linhas Mantidas"),
    #"Preenchido Abaixo" = Table.FillDown(#"Tabela Transposta",{"Column1"}),
    #"Colunas Mescladas" = Table.CombineColumns(#"Preenchido Abaixo",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Mesclado"),
    Cabecalhos = Table.Transpose(#"Colunas Mescladas"),
    Base2 = Table.Combine({Cabecalhos, Table.Skip(Base, 2)}),
    BaseExclusao = List.Buffer(Divisoes[Descricao]),
    BaseGrupos = List.Buffer(Table.SelectRows(Divisoes, each [Tipo] = "Soma")[Descricao]),
    Personalizar1 = Base2,
    #"Personalização Adicionada" = Table.AddColumn(Personalizar1, "Grupo", each if List.Contains(BaseGrupos, [Column1]) then [Column1] else null),
    #"Preenchido Abaixo1" = Table.FillDown(#"Personalização Adicionada",{"Grupo"}),
    #"Linhas Filtradas" = Table.SelectRows(#"Preenchido Abaixo1", each not List.Contains(List.Combine({BaseExclusao, {null}}), [Column1])),
    #"Cabeçalhos Promovidos" = Table.PromoteHeaders(#"Linhas Filtradas", [PromoteAllScalars=true]),
    #"Outras Colunas Não Dinâmicas1" = Table.UnpivotOtherColumns(#"Cabeçalhos Promovidos", {"DESCRIÇÃO|", "Column26"}, "Atributo", "Valor"),
    #"Dividir Coluna por Delimitador" = Table.SplitColumn(#"Outras Colunas Não Dinâmicas1", "Atributo", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Referência", "Tipo"}),
    #"Colunas Renomeadas" = Table.RenameColumns(#"Dividir Coluna por Delimitador",{{"DESCRIÇÃO|", "Conta"}, {"Column26", "Grupo"}})
in
    #"Colunas Renomeadas"

 

Por fim, em outra consulta nula cole o seguinte código e altere o endereço da sua pasta:

 

let
    Fonte = Folder.Files("C:\Users\vitor\Downloads\TestePasta"),
    #"Outras Colunas Removidas" = Table.SelectColumns(Fonte,{"Content", "Name"}),
    #"Texto Extraído Antes do Delimitador" = Table.TransformColumns(#"Outras Colunas Removidas", {{"Name", each Text.BeforeDelimiter(_, "."), type text}}),
    #"Personalização Adicionada" = Table.AddColumn(#"Texto Extraído Antes do Delimitador", "Personalizar", each Excel.Workbook([Content])),
    #"Colunas Removidas" = Table.RemoveColumns(#"Personalização Adicionada",{"Content"}),
    #"Personalizar Expandido" = Table.ExpandTableColumn(#"Colunas Removidas", "Personalizar", {"Name", "Data"}, {"Filial", "Data"}),
    #"Função Personalizada Invocada" = Table.AddColumn(#"Personalizar Expandido", "fTrataPlanilha", each fTrataPlanilha([Data])),
    #"Colunas Removidas1" = Table.RemoveColumns(#"Função Personalizada Invocada",{"Data"}),
    #"fTrataPlanilha Expandido" = Table.ExpandTableColumn(#"Colunas Removidas1", "fTrataPlanilha", {"Conta", "Grupo", "Referência", "Tipo", "Valor"}, {"Conta", "Grupo", "Referência", "Tipo", "Valor"}),
    #"Colunas Mescladas" = Table.CombineColumns(#"fTrataPlanilha Expandido",{"Referência", "Name"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Referência"),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Colunas Mescladas",{{"Referência", type date}, {"Filial", Int64.Type}, {"Conta", type text}, {"Grupo", type text}, {"Tipo", type text}, {"Valor", type number}})
in
    #"Tipo Alterado"

 

Creio que seja o que precisa.

 

Espero ter ajudado.

[Gabriel Fernandes Pereira]

Boa tarde, Vitor!

Tudo bem?

Certo! Quando vou carregar a 3ª consulta aparece esse erro na imagem.


 

[Gabriel Fernandes Pereira]

E tbm aparecem esses erros de divisões para a 2ª consulta

[Vitor Peralva]

Para resolver:

Renomear a primeira consulta para "Divisoes".

Renomear a segunda consulta para "fTrataPlanilha".