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.
Hi !
I wanted to transpose the excel data in Power BI so I get the Item, year , months , other data in individual columns, which I am struggling with now.
I want to show it like in the columns below.
Item Year Month Data
Projects Completed/month 2019 Oct 0
Projects Completed/month 2019 Nov 0
Projects Completed/month 2019 Dec 1
Projects Is completed/month 2019 Oct 0
Projects Is completed/month 2019 Nov 0
Projects Is completed/month 2019 Dec 11
Total Projects Is Completed 2019 Oct 0
Total Projects Is Completed 2019 Nov 0
Total Projects Is Completed 2019 Dec 11
Projects Completed/month 2019 Oct 0
Projects Completed/month 2019 Nov 0
Projects Completed/month 2019 Dec 0
Projects Is completed/month 2019 Oct 0
Projects Is completed/month 2019 Nov 0
Projects Is completed/month 2019 Dec 0
Total Projects Is Completed 2019 Oct 11
Total Projects Is Completed 2019 Nov 11
Total Projects Is Completed 2019 Dec 11
and so on.....
There are three sets on data on the table below and I want it all to be in columns so its appropriate when building the report based on the Year/month data
2019 | |||
Oct | Nov | Dec | |
Projects Completed/month | 0 | 0 | 1 |
Projects Is completed/month | 0 | 0 | 11 |
Total Projects Is Completed | 0 | 0 | 11 |
Total Projects | 353 | ||
Total Projects Is | 27039 | ||
2019 | |||
Oct | Nov | Dec | |
ETH - High | 3 | 6 | 88 |
Cummulative ETH - low | 7 | 11 | 88 |
Total Low ETH | 9 | 12 | 1 |
2019 | |||
Oct | Nov | Dec | |
ETH - low | 5 | 8 | 33 |
Cummulative ETH - low | 9 | 7 | 33 |
Total Low ETH | 6 | 5 | 1 |
Help will be greatly appreciated.
Solved! Go to Solution.
@Anonymous , when your data has the two beginning rows that you will like to bring as separate columns, (Kind of like two heading rows), you should do the following:
You may need to do a little bit of cleanup here and there afterwards.
I hope this works for you, if it does, kindly mark as solution to enable other people benefit from this.
@ahmedoye this is a great solution!
See below for the M query:
let
Source = Excel.Workbook(File.Contents("C:\Users\bfernandez1\Desktop\Temp\Book1.xlsx"), null, true),
Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet2_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", Int64.Type}, {"Column2", type text}, {"EWP's completed per month", Int64.Type}, {"Initial Isometrics completed per month", Int64.Type}, {"Initial Total Isometrics", Int64.Type}, {"Initial total EWP's", Int64.Type}, {"Total Isometrics", Int64.Type}, {"Column8", type any}, {"Column9", Int64.Type}, {"Column10", type text}, {"EWP's completed per month_1", Int64.Type}, {"Isometrics completed per month", Int64.Type}, {"Total Isometrics_2", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1", "Column2"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] <> "Column10" and [Attribute] <> "Column9")),
#"Filled Down" = Table.FillDown(#"Filtered Rows",{"Column1"})
#"Replaced Value" = Table.ReplaceValue(#"Filled Down","_1","",Replacer.ReplaceText,{"Attribute"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","_2","",Replacer.ReplaceText,{"Attribute"})
in
#"Replaced Value1"
Of course, change where there are strikethroughs with your own data.
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 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |