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

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:

 2019ACTJanuaryDEPTS 
      
      
Business UnitAcct 1Acct 2Acct 3Acct 4 Acct 5
Site 1100100100100100

Site 2

200

200200200200
Site 3300300300300300

 

AFTER: 

YearMonthBusiness UnitAcct 1Acct 2Acct 3Acct 4 Acct 5
2019JanuarySite 1100100100100100
2019January

Site 2

200

200200200200
2019JanuarySite 3300300300300300
1 ACCEPTED SOLUTION
danextian
Community Champion
Community Champion

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). 

danextian_0-1653370178075.png

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.




Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

9 REPLIES 9
danextian
Community Champion
Community Champion

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.

danextian_0-1653351843106.png

 




Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

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 1Acct 2Acct 3Acct 4Acct 5
 DEPTSDEPTSDEPTSDEPTSDEPTS
 ACTACTACTACTACT
 20192019201920192019
 JanuaryJanuaryJanuaryJanuaryJanuary
Site 1100100100100100

Site 2

200

200200200200
Site 3300300300300300
danextian
Community Champion
Community Champion

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). 

danextian_0-1653370178075.png

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.




Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

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
Community Champion
Community Champion

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.




Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

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
Community Champion
Community Champion

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... 




Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
danextian
Community Champion
Community Champion

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?

 

 2019ACTJanuaryDEPTS 



Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

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).

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors