Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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 E...
Need Help
  • Migasuke's avatar
    Migasuke
    3 years ago

    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.


AlB's avatar
AlB
Community Champion
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    AlB
    Community Champion
    3 years ago

    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.