cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shaunguyver Member
Member

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?

 

Data Screenshot.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Line chart showing values over time

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Line chart showing values over time

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

shaunguyver Member
Member

Re: Line chart showing values over time

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?

Super User
Super User

Re: Line chart showing values over time

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

shaunguyver Member
Member

rid oRe: Line chart showing values over time

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 Member
Member

Re: rid oRe: Line chart showing values over time

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!

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 323 members 3,163 guests
Please welcome our newest community members: