Forum Discussion
Line chart showing values over time
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?
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.
5 Replies
You need to unpivot the date columns in Power Query Editor and then you should be OK.
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?
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.
