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

[Gus]

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

[samdthompson]

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]

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 · Accepted solution]

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 

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:

[Gus]

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.

[samdthompson]

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.