cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Gus Frequent Visitor
Frequent Visitor

Comparing/plot two tables along a common time axis made of timestamps

Hi, I have two CSV files, both listing events of different kinds. Each line in the CSVs start with a timestamp. I can create nice diagrams from each of the CSV sources in Power BI Desktop, but I seem to be unable to show data from both sources on one chart.

 

To make it more clear, the first CSV file contains Windows Performance Monitor data of a computer, and another contains start and response times of various transactions (results of a load test). I want to plot, say, the change in CPU utilization over time and the change in the average transaction response time over time on a single chart.

 

Of course, timestamps are not the same in the two files (while one may be 01:23:43.444, the other is 01:23:43.446 or so). In addition, the second file may contain multiple rows with the same timestamp, if more than one transactions started at that same moment.

 

When I try to create a relationship between the two tables, I'm told the timestamp column is not unique. Clear. But how can I then relate or merge or do-whatever these sources to be able to use a common time axis and plot column values on a common chart?

 

Thx for reading

Gus

1 ACCEPTED SOLUTION

Accepted Solutions
samdthompson Established Member
Established Member

Re: Comparing/plot two tables along a common time axis made of timestamps

sure, not a problem. firstly dont split the columns. they are the key you need to join onto a separate table. I have just a a look and see that the milli second fomat is not available in powerbi, i might have missed something but i cant see it. To get around this problem i would make a text version of the right format in excel Excel_table.PNG

 

and create the joins to the time columns in your CSV files. This extra step needs to be done as the format of the csv time columns needs to be text since powerbi does not accept the .xx part of your time format. you can then join like this:

relationships.PNG

View solution in original post

5 REPLIES 5
samdthompson Established Member
Established Member

Re: Comparing/plot two tables along a common time axis made of timestamps

You are thinking along the right lines by trying to create a relationship. The missing bit is that you need to create a relationship with a time table. Each of the time columns need to join to a unique time value on a time table. When you have done that, use the time from the time table as say the x axis and then bring the measures in from the other tables as the values.

Gus Frequent Visitor
Frequent Visitor

Re: Comparing/plot two tables along a common time axis made of timestamps

Sounds promising! May I ask some tips for the details?

 

Do I just need to create a table with Hour, Minute, Second and Millisecond columns? Is there a way to populate this table (the duration of interest is roughly 3 hours)? If these assumption of mine are correct, do I need to split my times in the CSVs in hh:mm:ss.xxx form to the same set of four columns, or is there a smarter and simpler solution?

 

Sorry for asking such a lot of questions, I did not manage to find answers in documentation. Maybe it's because time intelligence is focused on year-month-day level rather than in minutes and seconds.

samdthompson Established Member
Established Member

Re: Comparing/plot two tables along a common time axis made of timestamps

Hi, no problems, questions are good.

 

You do need to make a new table. I am not sure how your data and data sources are set up but assuming you dont have a big datawarehouse type situation here are a couple of solutions DAX or excel:

 

1. on the modelling tab on the ribbon click new table; in the formula bar type time_table = summarize('csv_table','csv_table'[time]) - swap out the names for what ever in correct in your model. This will provide a remove duplicates type solution for all values in the table.

 

OR

 

2. use excel. Start in say a1 with time, then in a2 put in NOW() and format it to the hh:mm:ss.xxx format. copy down for however many rows you are wanting. turn into table and import.

samdthompson Established Member
Established Member

Re: Comparing/plot two tables along a common time axis made of timestamps

sure, not a problem. firstly dont split the columns. they are the key you need to join onto a separate table. I have just a a look and see that the milli second fomat is not available in powerbi, i might have missed something but i cant see it. To get around this problem i would make a text version of the right format in excel Excel_table.PNG

 

and create the joins to the time columns in your CSV files. This extra step needs to be done as the format of the csv time columns needs to be text since powerbi does not accept the .xx part of your time format. you can then join like this:

relationships.PNG

View solution in original post

Gus Frequent Visitor
Frequent Visitor

Re: Comparing/plot two tables along a common time axis made of timestamps

Thanks a lot! Because both CSVs contained timestamps and none of those were a subset of the other, and for I didn't know how could I use the SUMMARIZE function to merge the timestamps from both, I created an Excel file with a table of timestamps covering the entire range, and used it to make the relationships you mentioned with that table, and now it works.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,582)