Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jstein91694
Frequent Visitor

Merging and Plotting Multiple DataSets with Varying Time-step Increments

Hello,

I currently have created a PowerBI report with 4 different queries, each with data that increments by different timesteps (2 seconds, 1 minute, 2 minutes).

I have been trying for some time to set-up a relationship between all 4 queries so that I can plot all of the data on the same plot with the same DateTime axis.

Any help would be appreciated.

Best!

1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
Community Support

Hi, @jstein91694 

 

According to your description, I think you need to rename the four columns like date,time,timestamp,record. You need to make the column names and data types of the three tables exactly the same, and then append them.

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-janeyg-msft
Community Support
Community Support

Hi, @jstein91694 

 

According to your description, I think you need to rename the four columns like date,time,timestamp,record. You need to make the column names and data types of the three tables exactly the same, and then append them.

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@jstein91694 , Not very clear. You should split data and time into date and time in all tbales

 

Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))

 

Time = [datetime].Time
or
Time = Time(hour([datetime]),minute([datetime]),second([datetime]))

 

 

Join them with Date and time table and analyze together

Time table

https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

 

Create a Date Table. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Each of the queries has a Date and a Time column. Like I mentioned, one query may have formats of time that are like this: 3:50:02pm and it increments by 2 seconds, whereas another query has the format of time like this: 3:00:00pm and it increments by 1 minute. When I create my line plot and choose an axis, no matter which DateTime column I used, I am only ever to plot the data which corresponds to the query of the DateTime column I am using.

I would like to create a relationship between all queries, and be able to plot all of the queries' data on the same lineplot. (For queries that increment at 2 minute increments, I can average the data for each minute, if I can still plot the data on a DateTime axis that increments each minute)

@jstein91694 , can these be appened?

 

Can you share a small sample data and sample output in table format?

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

I tried appending and that did not work. Below are screenshots showing the format of a couple of the datasets.

jstein91694_0-1617375520116.pngjstein91694_1-1617375541560.png

 

jstein91694_2-1617375559427.png

Note how each Time row for each dataset increments by a different amount, so the time's only match up every so often for the datasets.

 

Did you ever get this resolved. I'm having a similar challenge where I have one data set that has increments of days, sometimes multiple datapoints per day. The other is basically every month. I'd like to plot both on the same graph and just connect a line between data points for the monthly while the daily can be on the X-axis.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.