Forum Discussion
Simple Beginners Question about a data set to work with
A beginners question. I'm new to PowerBI and this may be a stupid question
The data I'm working with is in this type of format (below). The data is in a series of columns.
If I have to Sum the data I'm summing horizontally which makes for a complex calculation (that I am partially struggling with).
Is the data in the wrong direction.
Should each line of data be a row.
I've about 2000 rows and 3 years worth of columns.
In Excel / Pivot Tables I'd total the years as seperate columns
| Brand | SubRegion | Country | Size | Demand Month | ProductId | 2024/01 | 2024/02 | 2024/03 | 2024/04 | 2024/05 | 2024/06 |
| Test | US | US | 300 | Jun-24 | 4 | 0 | 0 | 0 | 0 | 0 | 0 |
Anonymous , Your question is not stupid at all! It's a common challenge when transitioning from tools like Excel
The format of your data is known as a "wide format," where each time period (month in your case) is represented as a separate column. While this format can be convenient for certain types of analysis in Excel, it can be less efficient for use in Power BI, which often works better with "long format" data.
You can use unpivot columns option to correct this
To transform your data from wide to long format, you can use tools like Power Query in Power BI. Here are the steps to do this in Power BI:
Load your data into Power BI: Import your Excel file or other data source into Power BI.
Transform Data: Click on "Transform Data" to open Power Query Editor.
Unpivot Columns:
Select the columns that you want to keep as is (e.g., Brand, SubRegion, Country, Size, Demand Month, ProductId).
Right-click on the selected columns and choose "Unpivot Other Columns." This will transform your wide data into a long format.
Rename Columns: Rename the resulting columns to something meaningful, like "Date" and "Value."
2 Replies
Anonymous , Your question is not stupid at all! It's a common challenge when transitioning from tools like Excel
The format of your data is known as a "wide format," where each time period (month in your case) is represented as a separate column. While this format can be convenient for certain types of analysis in Excel, it can be less efficient for use in Power BI, which often works better with "long format" data.
You can use unpivot columns option to correct this
To transform your data from wide to long format, you can use tools like Power Query in Power BI. Here are the steps to do this in Power BI:
Load your data into Power BI: Import your Excel file or other data source into Power BI.
Transform Data: Click on "Transform Data" to open Power Query Editor.
Unpivot Columns:
Select the columns that you want to keep as is (e.g., Brand, SubRegion, Country, Size, Demand Month, ProductId).
Right-click on the selected columns and choose "Unpivot Other Columns." This will transform your wide data into a long format.
Rename Columns: Rename the resulting columns to something meaningful, like "Date" and "Value."Thanks for that bhanu_gautam . That was really helpful. I think my data is in a better position now (even if I have many125,000 rows now. I really appreciate it.