I have a data in this form in my excel table and now I am trying to create some visuals…
- User
I have a data in this form in my excel table and now I am trying to create some visuals on this basis in power BI:
Segment ID Name Category November'22 (FB Size) December'22 (FB Size) January'23 (FB Size) February'23 (FB Size) March'23 (FB Size) April'23 (FB Size) June'23 (FB Size) 14128064 IN_AL_Category Behaviours_Problem_1P_Dry Skin B&W 24,200 29,500 32,100 44,200 48,700 55,200 57,100 13949236 IN_AL_Category Behaviours_Problem_1P_Dry Skin & Moisturisers B&W 27,900 34,200 37,100 51,000 56,000 64,100 66,400
I want to create a line chart of month wise trend. The trouble is that Power BI is not recognizing the columns as month or something and I am not able to create it
- Assistant
To create a line chart of month-wise trend in Power BI, you'll need to transform your data so that the months are in a single column, and the values are in another column. Here's how you can do it:
- Open Power BI and go to Home tab.
- Click on 'Get Data' and select 'Excel'.
- Browse and select your Excel file and click on 'Open'.
- In the Navigator window, select the table having your data, and click on the 'Transform Data' button.
Now, you'll have the Power Query Editor window open:
- Select all the month columns (November'22, December'22, …, June'23).
- Right-click on the header of any of the selected columns, and click on 'Unpivot Columns'. This will transform your data to have a single 'Attribute' column holding the month names and a 'Value' column holding the corresponding values.
Your data should now look like this:
Segment ID | Name | Category | Attribute | Value
14128064 | IN_AL_Category Behaviours_Problem_1P_Dry Skin | B&W | November'22 (FB Size) | 24,200 14128064 | IN_AL_Category Behaviours_Problem_1P_Dry Skin | B&W | December'22 (FB Size) | 29,500 ...
-
Rename the 'Attribute' column to 'Month' and the 'Value' column to 'FB Size'.
-
You can also remove the '(FB Size)' part from the month names, if you want, by using the 'Replace Values' function.
-
After finishing the data transformation, click on 'Close & Apply' button to load the data to the Power BI.
Now, you can create a line chart using the transformed data:
- Go to the 'Visualizations' pane, and click on the 'Line chart' icon.
- Drag the 'Month' field to the 'Axis' area, the 'FB Size' field to the 'Values' area, and the 'Name' or 'Category' field to the 'Legend' area.
Now, you should see a line chart showing the month-wise trend for the different segments and categories in your data.