Forum Discussion
Week numbers as column headers? How to plot?
Greetings folks,
Novice Power BI user here, tasked with figuring all of this out on behalf of ze office.
I have a question for which the internet does not seem to have a solution:
We have excel books with tables with the week numbers as column headers. I've imported said tables into Power BI and I'm (desperately) trying to make a graph that plots these weeknumbers on the Y axis. However, Power BI insists that every week number is its own data set.
Let's assume I would be really great at what I do, what would my solution look like?
Thanks in advance
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
8 Replies
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
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)
You are a legend, this organizedthe data in a manner that is workable for me.
However, now I've run into the issue that Power BI thinks I want a total of everything in the column, making the graph look remarkably similar to just a straight horizontal line. This is not making me a happy camper. I'm trying to figure out how to dissuade Power BI from putting a total there and just plot the numbers like a good boy, but so far I havent figured out how to do that yet.
Any quick fixes for this?
Please check if this work,
Click on your Value field in the Y-axis section
Change "Sum" to Don’t summarize (if each week has only one value)
Hi Anonymous
Thank you for reaching out microsoft fabric community forum.
To plot week numbers from column headers in Power BI, load your Excel table and go to Transform Data. In Power Query, select the non-week columns (e.g., Product) and choose Unpivot Other Columns. Rename the resulting columns to WeekNumber and Value, and set WeekNumber as Whole Number. Apply changes, then use a line or bar chart with WeekNumber on the X-axis and your value on the Y-axis. If you see totals instead of individual values, change the aggregation to "Don’t summarize"—but only if each week has a unique value. Otherwise, aggregation (like Sum or Average) is expected.
If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.