Forum Discussion

facuns's avatar
facuns
New Member
4 years ago
Solved

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:

 

 

Into this:

 

Any tips?

 

Thank you all in advance!!

 

 

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

     

    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:

     



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

     

     

    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:

     

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


    And that is exactly what I wanted!

     

     

     

2 Replies

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

     

    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:

     



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

     

     

    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:

     

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


    And that is exactly what I wanted!