Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to Solution.
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
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 Columns
Then 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)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
84 | |
69 | |
68 | |
39 | |
37 |