This is an introduction to the use of Python and Power Query to create a calendar table for Business Intelligence purposes
Introduction
In the area of business intelligence and analysis, the need to have a calendar table for most reports can not be over-emphasized. The calendar table in most BI reports provides you with the flexibility to filter or should I say get certain kinds of information, that are not readily available in the original dataset. A calendar table increases productivity, efficiency, and data consistency for your crucial reporting processes.
Before now the use of DAX ( Data Analysis Expression) has been the major go-to when it comes to creating Calendar Table in Power BI. For today’s tutorial, I will show you three other ways to create a calendar table using Power Query and Python Script in your Power BI software.
Benefits of Calendar Table
Having a Calendar Table has different advantages, which I will list below.
It helps in getting an insight into the report by using certain features such as slicers.
You may filter your reports by properties like Year, Month, and any other aggregation of time you need for your organization across as many data tables as you require using Power BI Calendar Table.
Make up your own special time view that is distinct from a conventional calendar (such as “mmm yy”: Jul 22).
Section A: Calendar Table using Power Query
Step 1: Choose your Start Date
The Calendar is made up of two main conditions, which are the start date and end date. So, we will need to create a start date, which can also be called the minimum date.
CALENDAR(<start_date>, <end_date>)
Depending on your business need, the first step is by creating a start date for our calendar table.
From the Power Query Editor window, click on “Enter Data”. A new window pops open, this is where you enter your start date data. For this tutorial, we will use the start date as 1st January 2020 which will be written as “1/1/2020”.
After you have written all the necessary information, and then click on the OK button.
Step 2: Add the End Date Column
The End date is important in the calendar table because it usually has a starting date (minimum) and the end date (maximum).
For the end date, we need to create a new column, start by clicking on the “Add Column” tab in your Power Query Editor, then select a custom column. For the end date, we will be using today’s date which is the same as the Today() or Now() function usually found in DAX or Excel.
Since this tutorial requires Power Query we will need to write an M Language to create the end date, which can be found below.
=Date.From(DateTime.LocalNow())
Now, we have both the Start date and End Date columns, next is to change the datatype from ABC/123 to a date datatype. This is important because both columns need to be in date data type.
Step 3: Add a new Column for the Range between StartDate and EndDate
We need to create a new column that will obtain the range of dates between the start date and end date. This will be done by adding a new column in the Power Query Editor and selecting a custom column the same way we created the End Date column.
We then need to write an M language which will help create a range between the start date and end date columns.
={Number.From([StateDate])..Number.From([EndDate])}
The code above is used to create the Date column between the StartDate and EndDate.
Once the Date column is created, the new column will show as a LIST you will need to expand the column as new rows. So, select the top right corner of the Date column and highlight “Expand as New Rows”.
Now, we can delete the unwanted columns such as the “StartDate & EndDate Columns”.
Step 4: Adding new Columns
Year Column: From your, Power Query Editor click on the add column tab. Go to the right corner where the from Date & Time. Select the Date dropdown icon and pick Year.
Quarter and Month Number Columns: The same process used in creating the Year column is used for adding the two new columns. Just this time around you need to select the Quarter and Month.
Month Name Column: For the month column we want to have the months in words, we will need to add a custom column this time around.
From your add column click on “Custom Column” and put in the M language below.
Do not forget to change the datatype to Text when you are done.
=Date.MonthName([Date])
Days of Week Column: We need to add another custom column, with the M language code below. Also, do not forget to change the data type to Text.
=Date.DayOfWeekName([Date])
Step 5: Close & Apply:
We need to load the data we created back to Power BI. Click on your Home Tab then go to Close & Apply, this takes the data from Power Query to Power BI.
Now that you have your Calendar Table created you can head over to the modeling/relationship section of your BI and starts modeling.
In section B, I will show you how to create a Calendar Table in Power BI using Python scripts.
Section B: Calendar Table using Python Script
Python according to most developers is said to be their first choice when it comes to programming due to its easy usage and high community application.
It is an Open-Source, interpreted, object-oriented, high-level, dynamically semantic programming language.
Step 1: Download and Install the Anaconda Navigator
Anaconda Navigator is a desktop application with a friendly user interface that allows you to launch applications and easily manage conda packages, environments, and channels without the need to use a command line.
You can get the Anaconda Navigator by clicking Here, selecting your local machine operating system, and downloading the installer.
For this tutorial we will be using the Jupyter Notebook, one of the advantages of using the Jupyter Notebook, is that it already comes with some pre-installed libraries like Pandas, Numpy, Matplotlib, etc, which makes it easy for most Data professionals to use.
Step 2: Create your Calendar Table in Jupyter Notebook
For this code, we will be using 2 specific libraries, which are Pandas and date.
The code below is used in creating the calendar table.
#import packages
import pandas as pd #Data Manipulation
from datetime import date #Date&time
StartDate = ‘1/1/2021’ #StartDate
EndDate = date.today() #Enddate set as todays date
Date_Table= pd.DataFrame(pd.date_range(StartDate,EndDate,freq=”H”,tz=”UTC”), columns=[“Date”])
Date_Table[“Year”] = Date_Table[“Date”].dt.year
Date_Table[“Month”] = Date_Table[“Date”].dt.month
Date_Table[“Month_name”] = Date_Table[“Date”].dt.strftime(“%b”)
Date_Table[“Qtr”] = Date_Table[“Date”].dt.quarter
Date_Table[“Day_of_Week”] = Date_Table[“Date”].dt.dayofweek
Date_Table[“Hour”] = Date_Table[“Date”].dt.hour
Date_Table[“Week_name”] = Date_Table[“Date”].dt.strftime(“%A”)
Step 3: Creating Calendar Table in Power Query Editor
Ever since the announcement of Python integration with Power BI, most Python developers have resorter cleaning data in Power Query Editor using Python Script. This helps in cleaning certain kinds of data that are not easily cleaned using the regular Power Query Editor interface or Advanced Editor using the M language.
The following steps should be taken to create a calendar table in Power BI using Python
Creating a Python Virtual Environment in your Local Machine: By creating a virtual machine, it helps to prevent conflict of application in your Power BI. Doc or YouTube video of creating a virtual environment in your local machine.
Installing all necessary Libraries: We will need to install the following libraries in our new environment created.
pipenv install numpy pandas matplotlib seaborn scikit-learn
Set Python Script in your Power BI Directory: In your Power Query Editor follow these steps:
file>> options and settings>> options>> Python scripting
Test if Python Virtual Environment was Installed: We need to test if the virtual environment was properly installed in our local machine. This is done by opening our command prompt on our PC and activating the new environment.
Run Python Script to create Calendar Table: After all, the conditions are settled lately we need to run a Python script in Power Query Editor in creating the Calendar Table.
From your Power Query Editor, to go to the Transform Tab, at the top right you will see the Python Script.
All that is needed is to copy the code written in Jupyter Notebook inside Python.
After you click on the OK button, you will need to expand the table by clicking the Table in the Value Column.
With this, you are done with creating the calendar table. You can now Close and Apply your Calendar table to Power BI.
Conclusion
The importance of the calendar table can never be over-emphasized, it thereby provides you with the much-needed flexibility of your dataset. The article has shown us that you can also create calendar tables with both Python and Power Query depending on how comfortable you are with any of the processes.
Reference
https://towardsdatascience.com/using-python-in-power-bi-ee95a6b71443
