Line chart showing values over time

[shaunguyver]

I've tried to create something so simple but can't, so I need some help please!

I have a simple table (loaded from Excel) containing a list of properties, along with their cost over years (2013 through to 2018). 

I want to display this in a simple line chart, with each line representing a property, the X-Axis showing time, and the Y-Axis showing cost. When I try to do this, I'm unable to because it requires an X-Axis. I thought it would just know what the X-Axis is, given the layout of the table below. How can I achieve this?

[Greg_Deckler]

You need to unpivot the date columns in Power Query Editor and then you should be OK.

[shaunguyver]

Hi Greg,

Thanks for your quick reply. I've un-pivoted the columns in Power Query Editor, but Power BI won't let me apply the changes because it creates multiple duplicates of my entries in the table (due to the un-pivoting).

Am I doing something wrong?

[Greg_Deckler · Accepted solution]

No, probably not doing something wrong but it sounds like you have additional tables with relationships to this table and you had a one-to-many relationship and now you no longer have unique values. So, to fix that, what you generally need to do is create a bridge table with unique values. in it and relate the two tables via that bridge table. Thus you have a many-to-one and a one-to-many off of that bridge table creating a many-to-many relationship between your two original tables. 

You can do this in a couple different ways. In Power Query you can create a copy of your query, remove all columns except the key column and then do a remove duplicates. This will create your bridge table. Then you can remove the existing relationship, unpivot your table and then reform the relationship through the bridge table. You could also create a bridge table using DAX by creating a new table and using something like DISTINCT('Table'[Key Column]). That will also create a bridge table that you could potentially use although I recommend the first approach.

[shaunguyver]

Thanks Greg, I've just gone ahead and created a fresh dashboard to get the line chart working.

Problem is, I still can't get it to work! When I un-pivot the columns, all I can create is a line-chart with a single line for all buildings. When I drag 'building' into 'Legend' under fields panel it breaks up the line in the wrong way. I think this is because when I un-pivot the cost columns, it gets rid of all dates associated with those costs.

Using the data table above with un-pivoted columns, is there any way I can create a line-chart with X-Axis showing time, Y-Axis showing cost, and each line representing a building?

[shaunguyver]

Hey - I've solved it, I needed to split the original data source into the following columns only:

• Building
• Region
• Country
• Date
• CO2 Output

This is not ideal, as the original data source (Excel) will never be in this format. I'll work on your original suggestion above, re bridging tables. Thanks again for your help!