Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
nik751
Regular Visitor

Creating separate columns & transposing data using Power Query

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. 

nik751_0-1633181279029.png

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.

nik751_1-1633181004593.png

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

1 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 

 

ronrsnfld_0-1633311426391.png

 

 

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.

 

 

View solution in original post

5 REPLIES 5
ronrsnfld
Super User
Super User

  • 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?

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

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 

 

ronrsnfld_0-1633311426391.png

 

 

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
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors