Forum Discussion
Post PartisanHelp with how can I transform this Excel data?
Hi, ERing ,
Follow these steps to reach your goal:
You can learn more about Unpivot by the link: Unpivot columns
1. Renove top 2 rows:
2. Use first row as header:
3. Remove 2023 column:
4. Right click on top of Location column and then Unpivot Other columns:
5. Now your data should look like this:
Importante recomendations: Note that in the final result, you see months with suffixes like (Jan_1, Feb_2, etc.). This happens because when we promoted the first row to headers, Power Query added this index to prevent duplication in column names. While this is a good practice, in this case, we want the months as values, which is why we unpivoted the data. The suffix appears due to this. To fix this, I recommend renaming your Excel file columns to something like the example below:
You can learn more about Unpivot by the link: Unpivot columns
If this Reply satisfies your needs, please mark it as solution and give a Kudo.
Thank you
Super UserHi, ERing ,
Follow these steps to reach your goal:
You can learn more about Unpivot by the link: Unpivot columns
1. Renove top 2 rows:
2. Use first row as header:
3. Remove 2023 column:
4. Right click on top of Location column and then Unpivot Other columns:
5. Now your data should look like this:
Importante recomendations: Note that in the final result, you see months with suffixes like (Jan_1, Feb_2, etc.). This happens because when we promoted the first row to headers, Power Query added this index to prevent duplication in column names. While this is a good practice, in this case, we want the months as values, which is why we unpivoted the data. The suffix appears due to this. To fix this, I recommend renaming your Excel file columns to something like the example below:
You can learn more about Unpivot by the link: Unpivot columns
If this Reply satisfies your needs, please mark it as solution and give a Kudo.
Thank you
Post PartisanThis worked! Thanks!