Transpose excel data into columns in Power BI

[Anonymous]

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.

[ahmedoye · Accepted 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:

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 · Accepted 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:

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.

[bfernandez · Accepted solution]

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

[bfernandez]

Okay so if your goal is to get the data looking like you'd like in the table you posted, you can take another approach.

Keep the data as it is coming in from the source and do your editing in Power Query using the following steps:

Merge Month and Year:

Then Parse the MonthYear to get the start date of each MonthYear:

Then after Closing and Applying the steps, create a Matrix visual and populate it with the data as shown below:

[Anonymous]

Sorry if I had confused you in my original post, the table posted there was the data from the data source and the list I mentioned was how it should look like.

It should llook like the following in Power BI so I can create reports :

Item                                         Year   Month Data

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

[Anonymous]

When data comes as is from excel it looks like this, the months and years are in rows , how do I make it into the format you have in your first screen shot and then follow through.

[bfernandez]

This data seems very messy the way that it is presented or I am not getting the full picture. 

It is showing certain categories that are not grouped as they should be or I'm not seeing the information.

For example, there are multiple "Isometrics completed per month with different values under the same Oct 2019 column.

Can you elaborate a little bit more or give a bigger picture of the data you are giving?

[amitchandak]

Also refer : https://radacad.com/pivot-and-unpivot-with-power-bi

[bfernandez]

You can transpose data using the Transpose feature within Power Query.

You can fin it on the Transform tab, in the Table section.

[Anonymous]

My Data now looks something like this 

[bfernandez]

I cannot see anything attached to your reply, can you attempt to send the screenshots again?

[Anonymous]

[Anonymous]

 Thanks bfernandez, I am using the transpose function but its not arranging as I want. Tried several ways but its not re-arranging it the way I want it to be unfortunately.

Thanks