Forum Discussion
Solved
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 Hour...
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?
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!