Forum Discussion
Export data transformation
Hello,
I have a system that exports data in a poor fashion and I need help transforming this into readable data.
Standard export
| Date | Name | Total Hours | A Hours | B Hours | C Hours | D Hours |
| 01/01/2023 | John | 10 | 2.5 | 2.5 | 2.5 | 2.5 |
| 01/01/2023 | Maggie | 20 | 5 | 5 | 5 | 5 |
Desired output
| Date | Name | Hours |
| 01/01/2023 | John | 2.5 |
| 01/01/2023 | John | 2.5 |
| 01/01/2023 | John | 2.5 |
| 01/01/2023 | John | 2.5 |
| 01/01/2023 | Maggie | 5 |
| 01/01/2023 | Maggie | 5 |
| 01/01/2023 | Maggie | 5 |
| 01/01/2023 | Maggie | 5 |
For every A - D hours that has value greater than 0, it would need to create a new row and show this as hours for each available category.
Things to note - there will only ever be A - D hours, some hours in A - D will be 0/blank
Is this possible to solve? Ideally without a macro where the source table is pasted and another sheet in the same workbook shows the desired output
Hi Serdet
What HansVogelaar suggested gives the following. However, I'm not quite clear re. what you mean with For every A - D hours that has value greater than 0, it would need to create a new row and show this as hours for each available category
Does this reflect what you expect? If not what's wrong/should be changed?
4 Replies
It is possible to write a formula to unpivot using Excel 365 but it would be better to transform the data as part of the ETL process using Power Query.
= LET( attributes, Table1[[Date]:[Name]], headers, Table1[[#Headers],[Date]:[Name]], crosstab, Table1[[A Hours]:[D Hours]], rowNumber, SEQUENCE(1,ROWS(attributes)), repeats, SEQUENCE(1,COLUMNS(crosstab),1,0), valueField, TOCOL(crosstab), attrFields, REDUCE(headers, rowNumber, LAMBDA(acc,k, VSTACK(acc, CHOOSEROWS(attributes, k*repeats))) ), unpivotted, HSTACK(DROP(attrFields,1), valueField), FILTER(unpivotted, valueField<>"") )Unpivoting the value fields is the easy bit. Soring the repeats of the attribute fields is more of a mess.
p.s. I have just noticed I have 3 other files named unpivot so it is probably time to get a definitive treatment!
Hi Serdet
What HansVogelaar suggested gives the following. However, I'm not quite clear re. what you mean with For every A - D hours that has value greater than 0, it would need to create a new row and show this as hours for each available category
Does this reflect what you expect? If not what's wrong/should be changed?
- Unpivot works perfect. Thank you guys!
Try Unpivot Columns in Power Query.
