Forum Discussion
Week numbers as column headers? How to plot?
Load your Excel table into Power BI.
In Power Query Editor (Home > Transform Data):
- Select all columns that are not week numbers (e.g., Product, Region, etc.)
- Go to the Transform tab and click Unpivot Other Columns
You’ll now have:
- A column called something like Attribute (this holds your week numbers)
- A column called Value (this holds the data for those weeks)
Rename Attribute to WeekNumber and Value to something meaningful like Sales, Hours, or whatever applies.
Click Close & Apply.
Now in Power BI Report,
Create a line chart or bar chart
Drag WeekNumber to the X-axis
Drag your value field (e.g., Sales) to the Y-axis
Load your Excel table into Power BI.
In Power Query Editor (Home > Transform Data):
- Select all columns that are not week numbers (e.g., Product, Region, etc.)
- Go to the Transform tab and click Unpivot Other Columns
You’ll now have:
- A column called something like Attribute (this holds your week numbers)
- A column called Value (this holds the data for those weeks)
Rename Attribute to WeekNumber and Value to something meaningful like Sales, Hours, or whatever applies.
Click Close & Apply.
Now in Power BI Report,
Create a line chart or bar chart
Drag WeekNumber to the X-axis
Drag your value field (e.g., Sales) to the Y-axis
So I just tried this again, and now when I press apply, it's complaining I have duplicate values for certain columns... But isn't that the point? What do I do?
Thank you for your reply.
When I do this, I get an error saying:
The column [name] in the table [name] contains a double value. This isn't allowed if the column on the one side of many to one relation or the primairy key in a table
(This is a translation by yours truely, exact wording might differ on English version)