Data as matrix vs as unique columns

[NumericZero]

Hi everyone, 

I'm new to PowerBI and im struggling with a fairly basic concept, which is probably most easily explained in a quick example.

I have sales data that looks like this:

                 Q1/22    Q2/22      Q3/22    Q4/22

Steve          6.5          5.3            4.3          7.2

Bob            4.2           9.2            3.2          8.0

Janine         9.0          10.8          4.3          12.4

What I want to create is a line graph with the sales representatives names as the legend (so three colors), the quarters as the X axis and then the sales values as the Y axis. Very basic stuff! In Excel this is a matter of selecting the data and clicking on line graph and we're good to go. In PowerBI I can't find a way to achieve this without "unfolding" my input matrix into a more "list-y" matrix.

So: 

Name   Quarter   Sales Value

Steve    Q1/22      6.5

Steve    Q2/22      5.3

Steve    Q3/22      4.3

Bob      Q1/22      4.2

...

Changing a 3x4 matrix into a 12x3 matrix seems like a horribly inefficient way to go about something which the "weaker" Excel does in a split second? Could someone advise: am I overlooking something or is this "unfolding" really necessary to get a result in PowerBI?

Would be very appreciative about any insights. Thanks in advance from a beginner! 

Phil

[saud968]

In Power BI, the process of transforming your data from a wide format (like your initial matrix) to a long format (like the unfolded list) is known as "unpivoting." This transformation is necessary because Power BI's visualizations are designed to work with long format data, which allows for more flexibility and powerful data manipulation.

Here's how you can unpivot your data in Power BI:

Load your data into Power BI.
Go to the Power Query Editor by clicking on "Transform Data."
Select the columns that represent your quarters (Q1/22, Q2/22, etc.).
Right-click on the selected columns and choose "Unpivot Columns."
This will transform your data into the long format you described:

Name Attribute Value
Steve Q1/22 6.5
Steve Q2/22 5.3
Steve Q3/22 4.3
Steve Q4/22 7.2
Bob Q1/22 4.2
Bob Q2/22 9.2
Bob Q3/22 3.2
Bob Q4/22 8.0
Janine Q1/22 9.0
Janine Q2/22 10.8
Janine Q3/22 4.3
Janine Q4/22 12.4

After unpivoting, you can rename the columns to "Name," "Quarter," and "Sales Value" as needed. Now, you can create your line graph with the sales representatives' names as the legend, quarters as the X-axis, and sales values as the Y-axis.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[NumericZero]

Hello Saud, also thank you for your reply, all of you have been very helpful! 

[Kedar_Pande]

@NumericZero 

Go to Transform Data in Power BI to open Power Query.
Select your table.
Use the Unpivot Columns feature:
Highlight the quarter columns (Q1/22, Q2/22, etc.).
Right-click and choose Unpivot Columns.
This will restructure the data into a table with three columns: Name, Attribute, and Value.
Rename the columns:
Rename Attribute to Quarter.
Rename Value to Sales Value.

Click Close & Apply to load the transformed data back into Power BI.

X-Axis: Drag Quarter.
Legend: Drag Name.
Values: Drag Sales Value.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[NumericZero]

Hello Kedar, also thank you for your reply, all of you have been very helpful! 

[Bibiano_Geraldo · Accepted solution]

Hi @NumericZero ,
Welcome to Power BI! You're right in recognizing that Power BI works differently than Excel, especially when it comes to structuring and visualizing data. However, you don't need to manually "unfold" your data every time. There are simple ways to achieve the result you're aiming for without unnecessary transformations.

In Power BI, data needs to be in a "long" or tall format, where each row represents one observation. In your case, this means transforming the wide format (with multiple columns for each quarter) into a long format (with separate rows for each quarter and corresponding sales value).

If your data is still in the wide format (with quarters as columns), you can follow these steps to "unpivot" it:

• In Power Query, select the columns representing the quarters (e.g., Q1/22, Q2/22, etc.).
• Right-click on the selected columns and choose Unpivot Columns.
• This will create a new "long" table with Name, Quarter, and Sales Value as the columns.

Why This Is Necessary:
Power BI uses a columnar data structure and doesn't automatically recognize data in a matrix format the same way Excel does. The "unfolding" (unpivoting) process is necessary to tell Power BI that each sales value corresponds to a particular quarter and representative. This structure allows Power BI to create visualizations that are responsive to filters and slicers, which is one of its strengths.

You can lear more about UNPIVOT here 

[NumericZero]

Thank you not only for the solution, but also the reasoning behind it! Helps hugely for newbies like myself. 

"I can see clearly now the rain is gone" 😉