Beginner query on designing a chart where months are formatted as columns

[PowerBIUser14]

I'm trying to build a chart where I can see months on the X Axis so I can see a trend over time. Then have bars representing sold products and a line representing 'received' products to identify the gap. However, Power BI is not allowing for the same, any solutions would be much appreciated. 

Thanks (Data Set attached below). 

Sold
Product Type	Product Colour	Product Sub Colour	January	February	March
X	Blue	Red	1	1	0
Y	Blue	Green	0	0	1
Z	Green	Black	1	1	1
Received
Product Type	Product Colour	Product Sub Colour	January	February	March
X	Blue	Red	1	1	1
Y	Blue	Black	0	0	2
Z	Green	Red	1	1	1

[v-jingzhang · Accepted solution]

Hi @PowerBIUser14 

The current table format is not friendly for the chart you want. You have to transform the table with Power Query Editor first to make months locate in the same column. Open Power Query Editor from Home > Transform data, then you can do the following transformation steps to both tables. 

1. Select the first three columns and choose "Unpivot Other Columns" option. (Or select all month columns and choose "Unpivot Columns", which has the same effect.)

2. Rename the new Attribute column to "Month". Add Prefix and Suffix to this column to make it have month start date. Then change this column from Text type to Date type. 

3. Transform the other table in the same way. Then apply the change to Power BI Desktop. 

In Power BI Desktop, you can add a table to have all distinct month values from either of above table. And build relationships between it and Sold/Received tables on Month column. 

Then you can build the chart per your need. Remember to use Dim Month table's Month column on X-axis. 

I have attached a sample file at bottom for your reference. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.