cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nicolast29
Helper III
Helper III

Transpose different colunm from a excel file

Hi 

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

Capture d’écran 2022-11-29 205730.png

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

 

Thanks a lot

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

 

 

 

 

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @nicolast29 ;

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

vyalanwumsft_0-1669774842597.png

Or

vyalanwumsft_1-1669774857792.png

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.

View solution in original post

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

Hi, @nicolast29 ;

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

vyalanwumsft_0-1669774842597.png

Or

vyalanwumsft_1-1669774857792.png

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.

So beautifull !!!!

Thanks a lot

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors