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
h_l
Post Patron
Post Patron

create Date table on the basis of several tables

Hi there,

 

In our data, there is a Date table created on the basis of 1 loaded data:

Date = CALENDAR(MIN('Download'[RequestDate]),MAX('Download'[RequestDate]))
 
Now, there are more tables got loaded, every table contains a column with date, e.g.
'Sales'[PaymentDate], 'Sales'[OrderDate], 
'Subscribe'[SubDate],
'Shipment'[ShippingDate]
'Active'[ActiveDate]
 
PS: there is a date column created by using DAX.
 
Just wanted to ask:
if there is a way to create Date table, the date shall be from the earliest to the latest date in all columns in all table?
e.g.
Earlist date is RequestDate in Download table: 2023-1-1
Latest date is ActiveDate in Active table: 2023-6-7
all other date values in any table are in between above date.
 
Your advice is apprecaited.
H
2 ACCEPTED SOLUTIONS
Shalabh_Kushwah
Frequent Visitor

yes, this could be possible simply by following these steps.

1. Append all the date columns from all Tables which contains date.

2. Remove the Duplicate dates.

3. This will contain dates which are in all the tables. Lets suppose this table is named as DimDates.

 

To get all the dates without skipping any date in between. You can create a table.

 

DimCalendar = calendar(Min(DimDates[Date]),Max(DimDates[Date]))

 

Thanks and Regards 

Shalabh Kushwaha

 

View solution in original post

You can add a custom column with as "Date" in each table which is just a Column from 'Sales'[PaymentDate], 'Sales'[OrderDate],  'Subscribe'[SubDate], 'Shipment'[ShippingDate], 'Active'[ActiveDate] and then go for Appending and remove all other than Date column from Appended Table.

 

Thanks and Regards

Shalabh Kushwaha

View solution in original post

11 REPLIES 11
Shalabh_Kushwah
Frequent Visitor

Yes, Sure

 

The Way through which we can append the tables.

 

Step 1: Go to the "Transform Data".

Shalabh_Kushwah_0-1686639457569.png

 

Step 2: Click on "Append Query" in Power Query Editor.

Shalabh_Kushwah_1-1686639586808.png

 

Step 3: Select Tables which you want to Append.

Shalabh_Kushwah_2-1686641328675.png

 

Thanks and Regards

Shalabh Kushwaha

Hi Shalabh_kushwah,

 

Thanks for your reply. Yes I understand this is a way to append table, but here my question is, the Date is in different column names in different table, but not all in "Date" Column.

So, after append, there will be more than 1 columns, e.g. 'Sales'[PaymentDate], 'Sales'[OrderDate],  'Subscribe'[SubDate], 'Shipment'[ShippingDate], 'Active'[ActiveDate]

 

On the other hand, I just noticed there is a column created by using DAX, not in the raw data, so the Append function seems cannot process that column.

 

I was wondering if there is a way to combine all the Date into 1 column.

Please advise.

Thanks.

You can add a custom column with as "Date" in each table which is just a Column from 'Sales'[PaymentDate], 'Sales'[OrderDate],  'Subscribe'[SubDate], 'Shipment'[ShippingDate], 'Active'[ActiveDate] and then go for Appending and remove all other than Date column from Appended Table.

 

Thanks and Regards

Shalabh Kushwaha

Thanks again Shalabh_Kushwah for the workaround.

ryan_mayu
Super User
Super User

@h_l 

have you tried to use CALENDARAUTO()

CALENDARAUTO function (DAX) - DAX | Microsoft Learn

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks for reply. Seems this will return a full year calendar? but the actual data earliest date is not 1st of January, it can be any day, e.g. 3-April-2023. (up to what data will be loaded)

I don't know if we can have better solution for this. If you want the exact earliest and latest date.

maybe you can try to use below formular to get the earliest and latest date. and put them in the calendar function.

min(min(min(date1,date2),date3),date4)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




thanks ryan_mayu for the plan B

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Shalabh_Kushwah
Frequent Visitor

yes, this could be possible simply by following these steps.

1. Append all the date columns from all Tables which contains date.

2. Remove the Duplicate dates.

3. This will contain dates which are in all the tables. Lets suppose this table is named as DimDates.

 

To get all the dates without skipping any date in between. You can create a table.

 

DimCalendar = calendar(Min(DimDates[Date]),Max(DimDates[Date]))

 

Thanks and Regards 

Shalabh Kushwaha

 

Thanks for reply, would you please explain a little more about how to appened all date columns in different table into 1 created table?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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