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
Hi there,
In our data, there is a Date table created on the basis of 1 loaded data:
Solved! Go to Solution.
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
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
Yes, Sure
The Way through which we can append the tables.
Step 1: Go to the "Transform Data".
Step 2: Click on "Append Query" in Power Query Editor.
Step 3: Select Tables which you want to Append.
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.
have you tried to use CALENDARAUTO()
CALENDARAUTO function (DAX) - DAX | Microsoft Learn
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)
Proud to be a Super User!
thanks ryan_mayu for the plan B
you are welcome
Proud to be a Super User!
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?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
91 | |
84 | |
76 | |
64 |
User | Count |
---|---|
136 | |
113 | |
104 | |
98 | |
92 |