Convert Horizontal Spreadsheet to Vertical

**Barbara7300** · 03-08-2024, 03:28 PM

Hello,

I need help converting a Horizontal Excel Spreadsheet to a Vertical Format. It is a rather large spreadsheet with over 1,000 Payees (Sample Spreadsheet attached). Each individual entry "Payee" does not have all of the various "Fees". Viewing this Spreadsheet in a Vertical Format would make it much easier to work with. Any help would be appreciated.

**DJunqueira** · 03-08-2024, 05:34 PM

Welcome to the forum.

Converted with Power Query.

Formula:

let

    Fonte = Excel.Workbook(File.Contents("D:\Users\DomingosJunqueira\Downloads\Report.xls"), null, true),

    #"Merch Report1" = Fonte{[Name="Merch Report"]}[Data],

    #"Colunas Removidas" = Table.RemoveColumns(#"Merch Report1",{"Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17"}),

    #"Linhas Filtradas" = Table.SelectRows(#"Colunas Removidas", each ([Column2] <> null)),

    #"Cabeçalhos Promovidos" = Table.PromoteHeaders(#"Linhas Filtradas", [PromoteAllScalars=true]),

    #"Linhas Filtradas1" = Table.SelectRows(#"Cabeçalhos Promovidos", each ([Account] = null) and ([Payee] <> null)),

    #"Dividir Coluna por Delimitador" = Table.SplitColumn(#"Linhas Filtradas1", "Fee Description", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Fee Description.1", "Fee Description.2"}),

    #"Texto Aparado1" = Table.TransformColumns(#"Dividir Coluna por Delimitador",{{"Payee", Text.Trim, type text}}),

    #"Colunas Mescladas" = Table.CombineColumns(Table.TransformColumnTypes(#"Texto Aparado1", {{"Fee Description.1", type text}}, "pt-BR"),{"Fee Description.1", "Payee"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Mesclado"),

    #"Colunas Reordenadas" = Table.ReorderColumns(#"Colunas Mescladas",{"Mesclado", "ABA", "Account", "Account Type", "Fee Description.2", "Amount"}),

    #"Texto Aparado" = Table.TransformColumns(#"Colunas Reordenadas",{{"Fee Description.2", Text.Trim, type text}}),

    #"Texto em Maiúscula" = Table.TransformColumns(#"Texto Aparado",{{"Fee Description.2", Text.Upper, type text}}),

    #"Coluna Condicional Adicionada" = Table.AddColumn(#"Texto em Maiúscula", "Personalizar", each if Text.StartsWith([Fee Description.2], "ACH") then "ACH" else if Text.StartsWith([Fee Description.2], "MSC") then "MSC" else if Text.StartsWith([Fee Description.2], "NET") then "Network Pass" else if Text.StartsWith([Fee Description.2], "EX") then "Ex Warranty" else if Text.StartsWith([Fee Description.2], "WIRE") then "Wireless Modem" else null),

    #"Colunas Removidas1" = Table.RemoveColumns(#"Coluna Condicional Adicionada",{"Fee Description.2"}),

    #"Valor Substituído" = Table.ReplaceValue(#"Colunas Removidas1","$","",Replacer.ReplaceText,{"Amount"}),

    #"Tipo Alterado" = Table.TransformColumnTypes(#"Valor Substituído",{{"Amount", Currency.Type}}),

    #"Coluna em pivô" = Table.Pivot(#"Tipo Alterado", List.Distinct(#"Tipo Alterado"[Personalizar]), "Personalizar", "Amount"),

    #"Colunas Renomeadas" = Table.RenameColumns(#"Coluna em pivô",{{"Mesclado", "Payee"}}),

    #"Colunas Reordenadas1" = Table.ReorderColumns(#"Colunas Renomeadas",{"Payee", "ABA", "Account", "Account Type", "ACH", "Wireless Modem", "Network Pass", "Ex Warranty", "MSC"}),

    #"Colunas Removidas2" = Table.RemoveColumns(#"Colunas Reordenadas1",{"Account Type"})

in

    #"Colunas Removidas2"