Creating a Date Table

[Lucy64]

I have connected to data in SQL Server using DirectQuery.  I need to create a date table within Power BI but it appears this is not supported with DirectQuery mode.  Is that correct or am I missing something.  If there is a way, rather than creating it within the database then please let me know.

[v-qiuyu-msft]

Hi @Lucy64,

Currently, we are not able to create a calendar table when we connect to SQL Server database in DirectQuery mode, as the "New Table" feature is disabled. In your scenario, you need to create this calendar table on SQL Server database side.

Besides, you can vote on this similar idea: Create DAX measures, fields and tables in Direct Query mode.

Best Regards,
Qiuyun Yu

[ankitpatira]

@Lucy64 If you're using directquery to sql server and then creating date table via query editor then that will force you to switch your directquery mode to Import as currently you can't have both DQ and Import mode in single pbix file (creating date table via query editor is being treated as import mode). What you can do is out of query editor under Modelling tab -> New Table -> create date table using below code. This will let you have manually created date table without affecting your DQ mode query.

Date = CALENDAR (DATE(2000,1,1), DATE(2016,1,1))

[BhaveshPatel]

Thank you ankit. Something new to learn. Just curious to know, Can we use the approach presented by the Ruth in Direct Query Mode.

[BhaveshPatel]

Hi Lucy,

For creating a calender table within powerbi, Please visit this community blog by RuthPozuelo at here.