Creating separate columns & transposing data using Power Query

[nik751]

I am trying to create a report based on an excel file that has multiple sheets of data combined in one. I am trying to create different headers from this data & separate it in a way that I can best visualize data from it.
Following is the raw data from which I am trying to create separate headers. 

And, following is how I want the final data to be transformed and look like in Power BI. The color codes here are to identify the rows I am trying to transpose from the raw data.

I have tried removing columns, changing headers, transposing & using group by as well. Nothing seems to handle the issue. Also, I didn't find any related post on this seems like a common issue but no one seems to know the solution.

Any help is greatly appreciated.

Thanks.

-Niki

Some related content I found:
@edhans  
https://community.powerbi.com/t5/Power-Query/Manually-Assign-Certain-Rows-as-Column-Headers/m-p/1018... 

[ronrsnfld]

• Is the general format always the same in that there are always two rows for each Site and Model entry?
• Will it be reliable to separate Sites by the empty row that you show between sites.  

    If none of the above are true, is there some other convention to differentiate a Site name from a Model?

[nik751]

Hi, ronrsnfld

Both the assumptions are not true.

However, there is one way to separate Site Info that worked for me.

1. I created an index column.
2. Duplicated column 1
3. I know the specific no. of rows after which the Site name changes so I used a conditional statement & created a new 'Site' Column.

• = Table.AddColumn(#"Columns1", "Site", each if [Index] > 0 and [Index] <= 8 else "Site-XYZ")

Next, I created the 'Models' column- by using the Fill down function from Transform. Now, I can filter out the fields that are not required from the visual side for this specific 'Model' column.

I Still need to figure out the rest of the columns- Date, Workdays, Units/day, Planned, Build

[ronrsnfld · Accepted solution]

I have a solution and I posted it along with a long-winded explanation. For some reason, that and the code has disappeared.  So here is a link to the file on my OneDrive. There should be enough comments in the code for you to follow things along.  Separate and Transpose 

Here is the screenshot of the data I worked from and the results.

Note that when I created a "Table" from the data range, I selected that the table did NOT include headers.

[PhilipTreacy]

Hi @nik751 

I'd love to have a go at solving this for you but without some sample data I don't want to spend ages recreating what is in your image.  Plus doing that can lead to errors if I recreate the data/layout incorrectly.

Regards

Phil

[nik751]

Hi Phil,
I am pasting the raw data & expected data after transformation (in Power BI) here. Hope it helps.

Thanks.

Raw Data in Excel:

Site-ABC		Jan-21	Feb-21
	WorkDays	20	19
	Units/day	2	2
Model 1	Planned	5	2
	Built	3	1
Model 2	Planned	6	17
	Built	2	10
Site-XYZ		Jan	Feb
	Work Days	20	19
	Units/day	1	1
Model 1	Planned	5	6
	Built	4	5
Model 2	Planned	10	5
	Built	5	2

Expected Output in Power BI(after transformation):

Site	Date	Workdays	Units/day	Models	Planned	Build
ABC	Jan-21	20	2	Model1	5	3
ABC	Jan-21	20	2	Model2	6	2
XYZ	Jan-21	20	1	Model1	5	4
XYZ	Jan-21	20	1	Model2	10	5
ABC	Feb-21	19	2	Model1	2	1
ABC	Feb-21	19	2	Model2	17	10
XYZ	Feb-21	19	1	Model 1	6	5
XYZ	Feb-21	19	1	Model2	5	2