Forum Discussion
Tranposing Row to Column
Hello all.
I'm attempting to tranpose an excel model in essbase data per each month in a workbook and have 5 years worth of data. Look simple, perhap I'm missing something. Transpose, then duplicate column, Fill one column with year and the duplicate to Month. How can easily do this in power bi to table?
BEFORE:
| 2019 | ACT | January | DEPTS | ||
| Business Unit | Acct 1 | Acct 2 | Acct 3 | Acct 4 | Acct 5 |
| Site 1 | 100 | 100 | 100 | 100 | 100 |
Site 2 | 200 | 200 | 200 | 200 | 200 |
| Site 3 | 300 | 300 | 300 | 300 | 300 |
AFTER:
| Year | Month | Business Unit | Acct 1 | Acct 2 | Acct 3 | Acct 4 | Acct 5 |
| 2019 | January | Site 1 | 100 | 100 | 100 | 100 | 100 |
| 2019 | January | Site 2 | 200 | 200 | 200 | 200 | 200 |
| 2019 | January | Site 3 | 300 | 300 | 300 | 300 | 300 |
Hi Tevon713 ,
I intended to move those rows into columns as you can actually just show them as columns using the matrix visua. And also, the trouble with your approach is that if there are more than five Acct columns, you'll have to always get into the Query Editor and expand all those columns or they won't be included your dataset the next you refresh (see image below).If you want to show them as columns, go to Advanced Editor and replace the M-script with this:
let Source = Folder.Files("your path here"), #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".xlsx"), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.Contains([Name], "$")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Name", "Content"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Excel Workbook", each Excel.Workbook([Content])), #"Expanded Excel Workbook" = Table.ExpandTableColumn(#"Added Custom", "Excel Workbook", {"Data", "Item", "Kind"}, {"Data", "Worksheet", "Kind"}), #"Filtered Rows2" = Table.SelectRows(#"Expanded Excel Workbook", each [Kind] = "Sheet"), #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows2",{"Name", "Worksheet", "Data"}), #"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "Transformation", each let Original = [Data], Filtered= Table.SelectRows(Original, each [Column1] <> null and [Column1] <> ""), PromotedHeaders = Table.PromoteHeaders(Filtered, [PromoteAllScalars=true]), Year = Table.AddColumn(PromotedHeaders, "Year", each Original[Column2]{0}, Int64.Type), Month = Table.AddColumn(Year, "Month", each Original[Column4]{0}, type text) in Month), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Data"}), #"Expanded Transformation" = Table.ExpandTableColumn(#"Removed Columns", "Transformation", {"Business Unit", "Acct 1", "Acct 2", "Acct 3", "Acct 4 ", "Acct 5", "Year", "Month"}, {"Business Unit", "Acct 1", "Acct 2", "Acct 3", "Acct 4 ", "Acct 5", "Year", "Month"}) in #"Expanded Transformation"Change the data type of each column accordingly.
9 Replies
Hi Tevon713 ,
As mentioned, you'll need more than just transposing. In fact, in my sample pbix I didn't tranpose your sample data at all. I connected to a folder as a data source since there is more than one workbook involved prior to use Excel-related transformations.
Please refer to the links below:sample pbix - https://drive.google.com/file/d/10NHOcXi9YR5Unkk-PnRAul_nzauQEGXY/view?usp=sharing
files - https://drive.google.com/drive/folders/1_66CWc89iMglA0PlS33k5mIOLMW3iBdL?usp=sharing
Instructions:
Download the files and place then in a folder.Download the sample pbix and go to the Query Editor.
Replace the "your excel file here" with the path to the files. Please note that the path should be inside the double quotes. Inspect and study each step.
Hi @danextian,
Thanks, understood the combining multiple or all sheets form data source folder. Attempted to get data from folder GUI and hack the Transfrom Sample File in Power Query previously.
I did download and review each steps in query editor per your instructions. Is there a way to display accounts horizontally?
Else will re-access how data pull in essbase, for example newer model as below to show account horizontal not in single column.Acct 1 Acct 2 Acct 3 Acct 4 Acct 5 DEPTS DEPTS DEPTS DEPTS DEPTS ACT ACT ACT ACT ACT 2019 2019 2019 2019 2019 January January January January January Site 1 100 100 100 100 100 Site 2
200
200 200 200 200 Site 3 300 300 300 300 300 Hi Tevon713 ,
I intended to move those rows into columns as you can actually just show them as columns using the matrix visua. And also, the trouble with your approach is that if there are more than five Acct columns, you'll have to always get into the Query Editor and expand all those columns or they won't be included your dataset the next you refresh (see image below).If you want to show them as columns, go to Advanced Editor and replace the M-script with this:
let Source = Folder.Files("your path here"), #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".xlsx"), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.Contains([Name], "$")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Name", "Content"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Excel Workbook", each Excel.Workbook([Content])), #"Expanded Excel Workbook" = Table.ExpandTableColumn(#"Added Custom", "Excel Workbook", {"Data", "Item", "Kind"}, {"Data", "Worksheet", "Kind"}), #"Filtered Rows2" = Table.SelectRows(#"Expanded Excel Workbook", each [Kind] = "Sheet"), #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows2",{"Name", "Worksheet", "Data"}), #"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "Transformation", each let Original = [Data], Filtered= Table.SelectRows(Original, each [Column1] <> null and [Column1] <> ""), PromotedHeaders = Table.PromoteHeaders(Filtered, [PromoteAllScalars=true]), Year = Table.AddColumn(PromotedHeaders, "Year", each Original[Column2]{0}, Int64.Type), Month = Table.AddColumn(Year, "Month", each Original[Column4]{0}, type text) in Month), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Data"}), #"Expanded Transformation" = Table.ExpandTableColumn(#"Removed Columns", "Transformation", {"Business Unit", "Acct 1", "Acct 2", "Acct 3", "Acct 4 ", "Acct 5", "Year", "Month"}, {"Business Unit", "Acct 1", "Acct 2", "Acct 3", "Acct 4 ", "Acct 5", "Year", "Month"}) in #"Expanded Transformation"Change the data type of each column accordingly.
