Week numbers as column headers? How to plot?

[John_Doe3]

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

[Anonymous]

Hi @John_Doe3 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Anonymous]

Hi @John_Doe3 
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.

[John_Doe3]

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?

[BhavinVyas3003]

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)

[John_Doe3]

I tried that already, but no joy.

Now it may be possible that for some weeks, I have 2 bits of data. I'm not sure, it's quite a dataset to go through manually.

[BhavinVyas3003 · Accepted solution]

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

[John_Doe3]

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)

[John_Doe3]

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?