Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
Proud to be a Super User!
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.
Proud to be a Super User!
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.
Proud to be a Super User!
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.
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.
Proud to be a Super User!
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.
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...
Proud to be a Super User!
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 |
Proud to be a Super User!
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |