Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
So beautifull !!!!
Thanks a lot