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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
facuns
New Member

Transform excel data with merged headers

Hi! Im new with power bi and power query, and I couldn't find the answer to this question in the forum... What I need to do is to transform data that comes with this format:

 

facuns_0-1634776069954.png

 

Into this:

facuns_1-1634776136720.png

 

Any tips?

 

Thank you all in advance!!

 

 

1 ACCEPTED SOLUTION
facuns
New Member

After many hours paying around, I found the solution... I'm posting it here so it can help someone else... It is somehow related to the @amitchandak  document (thanks for that dude!). 
Starting with:

facuns_0-1634828697593.png

 

First of all, I deleted the first row with the name of the employees, and ser up the new first row as headers. That gives this:

 

facuns_1-1634828737921.png

Then, I selected "Fecha", and Unpivoted all the other columns, resulting in this:

 

facuns_2-1634828791276.png

 

Then the tricky one...  what I need to pivot and get the result I want, is to organize the columns in a way that I have the date in the first row, then a column with the number of the employee that each rows refers to, then a column with the headers  of the rows I want in my final table, and then the values. I managed to get that creating a new column that extract the number on the attribute column (that is the number of employee (1, 2, 3 etc). And then creating another column that extracts the first 3 characters of the atribute column (that will be the headers of the new columns when I pivot). Reordering that, and deleting the original attribute column, I got this:
facuns_3-1634829086294.png

 

Finally, I pivoted the columns selecting "First characters" and "Value" columns, to get this:

facuns_4-1634829191361.png


And that is exactly what I wanted!

 

 

 

View solution in original post

2 REPLIES 2
facuns
New Member

After many hours paying around, I found the solution... I'm posting it here so it can help someone else... It is somehow related to the @amitchandak  document (thanks for that dude!). 
Starting with:

facuns_0-1634828697593.png

 

First of all, I deleted the first row with the name of the employees, and ser up the new first row as headers. That gives this:

 

facuns_1-1634828737921.png

Then, I selected "Fecha", and Unpivoted all the other columns, resulting in this:

 

facuns_2-1634828791276.png

 

Then the tricky one...  what I need to pivot and get the result I want, is to organize the columns in a way that I have the date in the first row, then a column with the number of the employee that each rows refers to, then a column with the headers  of the rows I want in my final table, and then the values. I managed to get that creating a new column that extract the number on the attribute column (that is the number of employee (1, 2, 3 etc). And then creating another column that extracts the first 3 characters of the atribute column (that will be the headers of the new columns when I pivot). Reordering that, and deleting the original attribute column, I got this:
facuns_3-1634829086294.png

 

Finally, I pivoted the columns selecting "First characters" and "Value" columns, to get this:

facuns_4-1634829191361.png


And that is exactly what I wanted!

 

 

 

amitchandak
Super User
Super User

@facuns , That is not the ideal data, Try Like, Unpivot employee

Then move header up

 

Can you share sample data in text format

 

Also refer

https://kohera.be/power-bi/how-to-unpivot-twice/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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