I spoke about Python integration in Power BI at SQLSaturday #793 in Cape Town this past weekend. You can download the deck (which includes the notes) and the Python scripts (which include sample data) from the SQLSaturday site. One of the demos included the creation of a date table to support time intelligence DAX functions in Power BI. I thought the example would be interesting for a number of reasons:
- Time intelligence is one of my favorite features of Power BI (Power Pivot & SSAS)
- A few examples of M script to create date tables already exist (it would be interesting to compare)
- Illustrate some handy dataframe capability (including manual creation of utility dataframes, and treating dataframes like tables to perform join operations)
Before we start, lets quickly recap the basic criteria for a date table in Power BI:
- A separate date table is required (and joined to the table to calculate against)
- The date table must be at a day level (1 row equals 1 day)
- The days must be contiguous (no gaps even if your data is not daily)
- The days must be available for full years (1 Jan to 31 Dec)
- A column of data type ‘Date’ must exist on the date table
Step 1: Create a list of sequential dates
For this we use the Pandas date_range method.
The output shows the first 10 and last 10 elements of the list, the data type and the number of elements (length).
Step 2: Create a dataframe from the list
A list can easily be converted into a dataframe. The result is a dataframe with one column.
The column is assigned a default name of 0. The second line renames the column.
Step 3: Derive additional columns
For this step we make use of the string format time (strftime) method to derive required columns from the Date column. Where a numeric data type column is required, we explicitly convert the column using the dataframe apply function.
At this step you already have a basic date table. In many cases financial calendars do not end in December. In this example the financial year ends in March.
Step 4: Create a financial calendar lookup dataframe
I took a different approach with the financial calendar columns because I could easily do this inside the script with Python. Instead of using conditional evaluation (if, case, switch, etc), I decided to create a lookup “table” that a could join to. The lookup table would either provide the column I need, or help me create the column I need.
In the creation of this fy lookup dataframe, each column is provided as a list input i.e. each row in the code adds a column to the dataframe. All the lookup dataframe does is remap the financial periods to start in April (calendar month 4). The FinYearDif column will help us calculate the financial year in the next step.
Step 5: Add the financial year periods
We do this by doing a left outer join to the lookup dataframe. This is achieved through the merge method. We specify MonthNumber as the join column and outer as the join type. Finally we derive the financial year and then drop the helper column (axis=1 indicates the column axis).
Add to Power BI with the Python Script …
Bonus step: Persist result to a SQL Server database
I hinted previously that its easy to persist the data to a SQL Server database using the dataframe.to_sql method. Add this after your code:
You may want to revisit the post on SQL Server connectivity. The last line of code is all that’s new.
A thought
I added the to_sql method and used this in my Python script for Power BI. If this will be supported in the Power BI Service, I may consider writing to SQL Server in the Python script, then reading the result from SQL Server. I could even run stored procedures that only load deltas to SQL Server. Should something like this be allowed in the Power BI Service, it could mean that a Power BI refresh also runs my (ETL) process to persist data to SQL Server.
Download the script
As mentioned at the start of this post, this was part of a SQL Saturday demo. The complete script (and more) is available from the SQL Saturday website for download.
I’ll unpack the other examples in future posts.
Data Ideas 
Leave a comment