Forum Discussion
TRANSFORM MULTPLE COLUMNS INTO LESS COLUMNS, MORE ROWS
I am working with a ghastly excel spreadsheet which holds daily totals in multiple columns.
I have manipulated the table in Power Query to simplify the data into three sets - Date, Attribute and Total. However these three sets of information are replicated in multiple columns still - one for each month of the year. The rows hold the totals for each day of that month. I have some sample data attached.
What I want is for there to be only three columns: Date, Attribute and Total, with all the information from the other columns appended to the bottom. Is this possible?
Anonymous , rename the column with suffixes like 1 ,2 etc
Create the first row as a header
unpivot all rows
split the attribute on space (if you have created date 1, attribute 1)
then pivot the attribute.1 and value column
1 Reply
- amitchandak
Super User
Anonymous , rename the column with suffixes like 1 ,2 etc
Create the first row as a header
unpivot all rows
split the attribute on space (if you have created date 1, attribute 1)
then pivot the attribute.1 and value column