Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nicolast29
Helper IV
Helper IV

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors