Forum Discussion
Help with how can I transform this Excel data?
I've been given the Excel data below as a data source to use in a report. I need to transform the data so it's in a useful structure.
I know I can use "first row as headers" but I'm unsure what to do after that.
I need to get the data so that I have the following columns
1. Location
2. Month & year
3. Website visits
Expected Format
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
3 Replies
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
This worked! Thanks!
I would first delete the 2023 total column, as you will be able to calculate this later
Then i would go to Transform > Transpose
Select the columns with the locations and Unpivot other ColumnsThen I would need to tidy up the header names manually
My PowerQuery Code looks like the below:
let Source = // your source here, #"Transposed Table" = Table.Transpose(Source), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Column1", "Location"}, "Attribute", "Value") in #"Unpivoted Columns"My data looks like:
Note that because of the way your data is structured, when you click "transpose", the locations column is actually the header for the months (but this can easily be fixed by renaming the column)
