Transpose excel data into columns in Power BI

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:

1. Transpose the Table: This will bring the two heading rows into columns but will make the previously good column go into rows with the items on the first row. Not to worry, apply the next steps
2. Go to Transform Tab and Use First Row as Header: This will make the items on the first row become the heading
3. Select the two new columns, right click and select Unpivot Other Columns: This will now make you have a new column with the items in the headings becoming the new column, and the values right in front of them as a new column.

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.