Forum Discussion
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
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:
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.
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.
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.
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:
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.
