Transpose different colunm from a excel file

[nicolast29]

Hi 

I nhave an excel file where the month are on different column on the same sheet

what would be the best way to transpose it in one table

Thanks a lot

https://1drv.ms/x/s!AoqtZHsX4BzvhJQaQsHmzzsA3n9O1Q?e=yItI8B

[v-yalanwu-msft · Accepted solution]

Hi, @nicolast29 ;

You could  refer to my method, I can generally turn into two forms, you can refer to it.

Or

let
    Source = Excel.Workbook(File.Contents("C:\Users\Administrator\Downloads\Budget Transpose.xlsx"), null, true),
    Tableau1_Table = Source{[Item="Tableau1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Tableau1_Table,{{"Colonne1", type text}, {"Colonne2", type text}, {"Colonne3", type any}, {"Colonne4", type any}, {"Colonne5", type any}, {"Colonne6", type any}, {"Colonne7", type any}, {"Colonne8", type any}, {"Colonne9", type any}, {"Colonne10", type any}, {"Colonne11", type any}, {"Colonne12", type any}, {"Colonne13", type any}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Blank Rows", [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Column6", "Column10"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"-", type text}, {"-_1", type text}, {"Janvier-Ca", Int64.Type}, {"Janvier-Budget", Int64.Type}, {"Janvier-Ecart %", type number}, {"Février-Ca", Int64.Type}, {"Février-Budget", Int64.Type}, {"Février-Ecart %", type number}, {"Mars-Ca", Int64.Type}, {"Mars-Budget", Int64.Type}, {"Mars-Ecart %", type number}}),
    #"Filled Down1" = Table.FillDown(#"Changed Type1",{"-"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down1", each [#"-_1"] <> null and [#"-_1"] <> ""),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"-", "-_1"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Month", "cate"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Month", type text}, {"cate", type text}})
in
    #"Changed Type2"

let
    Source = Excel.Workbook(File.Contents("C:\Users\Administrator\Downloads\Budget Transpose.xlsx"), null, true),
    Tableau1_Table = Source{[Item="Tableau1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Tableau1_Table,{{"Colonne1", type text}, {"Colonne2", type text}, {"Colonne3", type any}, {"Colonne4", type any}, {"Colonne5", type any}, {"Colonne6", type any}, {"Colonne7", type any}, {"Colonne8", type any}, {"Colonne9", type any}, {"Colonne10", type any}, {"Colonne11", type any}, {"Colonne12", type any}, {"Colonne13", type any}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Blank Rows", [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Column6", "Column10"}),
    #"Filled Down1" = Table.FillDown(#"Removed Columns",{"Column1"}),
    #"Transposed Table" = Table.Transpose(#"Filled Down1"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"-", type text}, {"-_1", type text}, {"Janvier-Ca", Int64.Type}, {"Janvier-Budget", Int64.Type}, {"Janvier-Ecart %", type number}, {"Février-Ca", Int64.Type}, {"Février-Budget", Int64.Type}, {"Février-Ecart %", type number}, {"Mars-Ca", Int64.Type}, {"Mars-Budget", Int64.Type}, {"Mars-Ecart %", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [#"-_1"] <> null and [#"-_1"] <> ""),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"-", "-_1"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type2", List.Distinct(#"Changed Type2"[Attribute.2]), "Attribute.2", "Value", List.Sum)
in
    #"Pivoted Column"

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nicolast29]

So beautifull !!!!

Thanks a lot