Tranposing Row to Column

[Tevon713]

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

[danextian]

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.

[Tevon713]

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

[danextian · Accepted solution]

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.

[Tevon713]

Hi @danextian,

Thank you, make sense. I have over 50 accounts. Thought be easier if I display horizontally for calculating average and creating measure calculation.

[danextian]

What kind of calculations do you do that you have to have the Accouns columns horizontally? Vertical arrangement makes more sense for me as it will make it easy to slice and dice the data with lesser measures too.

[Tevon713]

Maybe I'm overthinking still thinking as excel modeling. Calculate average per account per site/region. And some measure calculation calling other account into the forumula at specific time period. The end goal to have filters and display those region site average and cumulative average and/or measure. But also have user selection 2 years and compare the variance.

Thanks again for all your help. Much appreciated.

[danextian]

You should be able to do those using DAX. You can make  a separate post for your questions if you don't know what to do. Use this as a guide so you can get a better/faster response:
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071... 

[danextian]

Hi @Tevon713 

Hi it looks like you need more than just transposing (or if that is the proper term for your use case).  Are the below always in the same cells in your excel sheet? How many workbooks are involved?

2019

ACT

January

DEPTS

[Tevon713]

Hi @danextian. There are 12 worksheets for each month in calendar year. So there be Jan-Dec worksheets in a workbook for same year. But yes same cells the only thing that change is year. Same account and business units.  Right now I have 5 workbook (2017-2022).