Transform a static calendar table imported from Excel into a dynamic one

[Anonymous]

I have recently imported a data model with all tables and measures from Excel to Power BI, which worked very well.

In Excel, in order to generate my date table, I had manually created a regular Excel table with one column that contained all the dates from 2018 to 2026, added it to the data model and then modified it with Excel Power Pivot, adding a large number of dependent calculated columns and measures. It was likewise migrated to Power BI without problems. 

In Power BI Desktop, I would now like change this imported static date table into a dynamic date table that is automatically created and updated depending on the the dates that the data model contains.

Generally, such a dynamic table can be created by using the "New Table" command followed by the CALENDARAUTO() function in the data pane of Power BI. The problem for me is that my underlying static dates column (between 2018 and 2026) comes from the Power Query Editor and remains static there after the import, because DAX functions like CALENDARAUTO() are not applicable in the Power Query Editor.  Hence, the entire date table with its calulated columns remains static in the data model of Power BI.

So how can I exchange the first static column of my dates table through a dynamic one while retaining all of the dependencies of the subsequent calculated columns and measures?

[AlB]

Hi @Anonymous 

One option would be to substitute the static table in PQ with the list of dates for a dynamic query that does the same as CALENDARAUTO(), i.e. check the min and max dates in the other tables in your model and generate the list of dates

A second option would be to create a date table from scratch in Power Query and forget about the calculated columns in DAX. There are many example you can download directly from the internet.

If  you share the pbix (removing sensitive info), I can have a look to try option 1 

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

Thanks for your reply, @AlB .

Basically, my reply to @Migasuke applies to your comment as well.

But in addition, I am much interested in your second option, because that might lead to the solution: creating a dynamic date table in Power Query. How do I do that? Power Query does not accept DAX, only M.

[AlB]

@Anonymous 

1. I do not see how it does. For the first option I am talking about making the changes in Power Query, not in DAX

2. First Google hit:
https://gorilla.bi/power-query/date-table/

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Migasuke]

Hi @Anonymous ,

I use very simple formula for my Calendar Table. It's a DAX solution but works every time:
Calendar = CALENDAR(MIN('Table'[MyDates]),MAX('Table'[MyDates]))

MyDates is ofcourse a column, which contains dates I want to align with. So depends on my Table and dates, calendar will automaticaly adjust.

[Anonymous]

Thanks for your reply,  @Migasuke .

My problem is not that I do not know how to create a dynamic date table in general. The expression that you suggest would work, if only I could apply it.

My problem is that I cannot apply any DAX expression that creates a table at all, because there is already an entire date table. By some turn I need to replace its static [dates] column by a daynamic one. I have to snatch away the static [dates] column, so to say, and put in a dynamic one insted, while retaining all of the column dependencies that I have throughout my data model.

The core of the problem is that my static [dates] column does not come from the Power BI main interface (i.e. the data tab in the left pane), but from the Power Query Editor of Power BI. There it lies in form of a table with one column and is transferred into Power BI. And that is where I currently struggle to replace it by a dynamic one.

[Migasuke · Accepted solution]

@Anonymous,
Okay here is what you can do:

1. Prepare your fact table with Dates, something like I have in my example:

2. Retrieve Min and Max (Start and End dates) in numeric format:

3. Create a table (Calendar), where you have Start Date and End Date in two columns:

4. Create new column with all dates between those two dates:
5. Clean the table a bit with removing extra tables:


I can't attach file here, but in case you want - send me PM and I can send you there.