In one of my previous RallyPoint Articles, Star Schema’s and Fact vs Dimension Tables, I introduced you to the Star Schema and the importance of Dimension Tables. In case you missed it or need a refresher, a Dimension Table is a static reference table used within a Star Schema Data Model to take the load off the main Fact Table (containing specific event information).
we need multiple reference tables (Dimension Tables) spread around the Fact Table, and one of the most required references is Time. Almost every Fact Table will contain some type of “time” reference containing time-specific data on when specific event/s occurred. While Power Bi does have built-in Date Splicing Functionality, we are limited to only slicing by Year > Quarter > Month > Day. It can also become difficult to compare rows between Fact Tables if there is no common Date Dimension to slice by.
This issue can be solved quickly with an imported Power Query script specifically designed to give you your desired date range with the flexibility to drilldown.
After that, choose the PowerBi field you want the Time Dimension Table added to and add a Blank Query:
Next, in the newly opened Blank Query, navigate to the View tab and click Advanced Editor.
Following that, open the script in a text editor (such as notepad) and copy and paste the contents into the Advanced Editor.
The only parts that require manual input are the FromYear and ToYear. Change these dates to suit your needs and click done.
What you are left with is an extensive Time Dimension Table that acts as a centralized time splicer and can be utilized to provide in-depth analysis of your data! This may not seem important for your simple 2 table dataset but once you start to add complexity and additional data from other sources the Time Dimension Table pays for itself!
As always if you have any issues with this, or anything else data related, feel free to reach out!