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

[Anonymous]

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 - 

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? 

[Anonymous]

Hi @Anonymous,

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

Thanks,

Sunil

[v-xicai]

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.

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

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

Ziyad,

You can follow the below steps to get this done.

I have created 2 tables Sheet1 and Sheet2

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)

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

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

If this solves your problem please accept this as solution.

Thanks,

Sunil

[Anonymous]

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]

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

[sanct]

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/