cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Creating a date table by combining two existing date tables from different data sources

I am workig with sensor data from two data sources. When I plot them in the line chart using the date column from one table or the other, I get the following - 

 

Line chart.PNG

 

Is there a way to create a relationship with these dates or combine the date columns into one so that I can use the same date axis for values from both data sources? 

 

Del Date.PNGEFH Date.PNG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Ziyad,

 

You can follow the below steps to get this done.

I have created 2 tables Sheet1 and Sheet2

Step1.PNG

Using these 2 tables i have created another table using Append option fro Query editor Edit Queries ==> Append Query (using Append Queries in new - You can append in same table as well)

Step2.PNG

This will have data like below (i have used the comment column only to indentify the different values. You can use your own)

Step3.PNG

Then click on "Close and Apply". Create your report using this new table.

Step4.PNG

If this solves your problem please accept this as solution.

Thanks,

Sunil

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hi @Anonymous,

 

If this solves your problem. Can you please give the kudos ?

 

Thanks,

Sunil

v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

You can create a calculated table Intermediate_table to union the table1 and table2 to get distinct Date , and create relationships between the three tables like picture below. Note that changeing the Cross filter direction of relationships between the tables from Single to Both, recommend do this for simplifying the data model or further calculation, which will take these tables treated as a single table.

 

10.png

Intermediate_table = DISTINCT(UNION(SELECTCOLUMNS(table1 ,"Distinct_Date",table1 [DELDate]),SELECTCOLUMNS(table1 ,"Distinct_Date",table1 [EFHDate])))

 

11.png

 

 

 

 

 

 

12.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Amy

 

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

 

 

Anonymous
Not applicable

Ziyad,

 

You can follow the below steps to get this done.

I have created 2 tables Sheet1 and Sheet2

Step1.PNG

Using these 2 tables i have created another table using Append option fro Query editor Edit Queries ==> Append Query (using Append Queries in new - You can append in same table as well)

Step2.PNG

This will have data like below (i have used the comment column only to indentify the different values. You can use your own)

Step3.PNG

Then click on "Close and Apply". Create your report using this new table.

Step4.PNG

If this solves your problem please accept this as solution.

Thanks,

Sunil

Anonymous
Not applicable

Hi Ziyad,

 

You can create a new table using these dates like date dimension table and then can create a relationship between this date dimesion table and your existing tables. Pull the date column from date dimension table . This will resolve your problem.

If this solves your problem please mark this as solution so that other person can get immediate solution for same kind of issue.

 

Thanks,

Sunil

Anonymous
Not applicable

That is the idea, but I cannot figure out the query to combine the two existing date tables into one. Any ideas? 

sanct
Frequent Visitor

Following up on @Anonymous 's response, this blog post provides a decent overview of how to create a date dimension:

https://www.mssqltips.com/sqlservertip/4857/creating-a-date-dimension-table-in-power-bi/

 

There's also this one, which I prefer since I can easily adapt it to new data or to different reports (this is the date model I currently use):

https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

 

Based on your data, you might need to modify your date/time column to just a date column to make it work. If you need both date and time, you might consider this advice (which would require you splitting your date/time column into date and time columns supported by date and time dimensions.):

https://ginameronek.com/2014/10/01/its-just-a-matter-of-time-power-bi-date-time-dimension-toolkit/ 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors