Every BI Solutions will have a date dimension table data with other business data. Date dimension is one of the mandatory table as most of the analysis are based on date dimension table.
We can generate the date dimension table in all the scenarios. This post explains you that how we can generate the date dimension table in Power BI.
We have different ways to generate date dimension table. Here I explained two different ways.
Create a table by creating individual column manually
Use New table option and start to create the columns with below expressions.
|DateAsInteger||FORMAT ( [Date], “YYYYMMDD” )|
|Year||YEAR ( [Date] )|
|Quarter||“Q” & FORMAT ( [Date], “Q” )|
|YearQuarter||FORMAT ( [Date], “YYYY” ) & “-Q”
& FORMAT ( [Date], “q” )
|Monthnumber||FORMAT ( [Date], “MM” )|
|YearMonthnumber||FORMAT ( [Date], “YYYY/MM” )|
|YearMonthShort||FORMAT ( [Date], “YYYY/mmm” )|
|MonthNameShort||FORMAT ( [Date], “mmm” )|
|MonthNameLong||FORMAT ( [Date], “mmmm” )|
|DayOfWeekNumber||WEEKDAY ( [Date] )|
|DayOfWeek||FORMAT ( [Date], “dddd” )|
|DayOfWeekShort||FORMAT ( [Date], “dddd” )|
Single Script to Generate Date Table
Add the below script in New table expression.
VAR fiscal_year_end_month = 3
CALENDARAUTO ( fiscal_year_end_month ),
“DateAsInteger”, FORMAT ( [Date], “YYYYMMDD” ),
“Year”, YEAR ( [Date] ),
“Quarter”, “Q” & FORMAT ( [Date], “Q” ),
“YearQuarter”, FORMAT ( [Date], “YYYY” ) & “-Q”
& FORMAT ( [Date], “q” ),
“Monthnumber”, FORMAT ( [Date], “MM” ),
“YearMonthnumber”, FORMAT ( [Date], “YYYY/MM” ),
“YearMonthShort”, FORMAT ( [Date], “YYYY/mmm” ),
“MonthNameShort”, FORMAT ( [Date], “mmm” ),
“MonthNameLong”, FORMAT ( [Date], “mmmm” ),
“DayOfWeekNumber”, WEEKDAY ( [Date] ),
“DayOfWeek”, FORMAT ( [Date], “dddd” ),
“DayOfWeekShort”, FORMAT ( [Date], “dddd” )
The above script will generate a table with all the columns. We can customise the table by modifying the expression.
In Power BI, you can use the pre-defined function CALENDARAUTO() to create date table with a column called “Date”. It will generate a date for current 1 year by default.
This above function will only work when you have a model with at least one column with date or datetime data type.
In the below screenshot, I tried to use CALENDARAUTO() function in an empty model.
You can see the warning message above.
“CALENDARAUTO() function can not find a base column of DateTime type in the model”
In the same scenario, I just added a simple table with one date data type column then I used the CALENDARAUTO() in a new table and this time I got the expected result.
Happy Learning. Share your comments.
This article explains to you that how we can use SharePoint list as a source for Power BI and also extending the Power BI report to embed on SharePoint.
I am following the same strategy in all my blogs. Reading a big paragraph is tough than a step by step points as it is easy to follow and remember.
Follow the below steps,
Step 1: Create a SharePoint list as per your business needs. It can be simple or complex with calculated columns. In my case, I created a simple SharePoint list with some sample content as like below.
Step 2: Get the SharePoint URL where the list is created.
Step 3: Open Power BI Desktop and click Get Data.
Step 4: Choose SharePoint Online List, if you use O365. Click Connect
Step 5: Pass the O365 SharePoint URL and credentials.
Step 6: Choose the list and click Load.
Step 7: It will load all the columns from the SharePoint list. Choose the visualization and map with the respective fields.
Step 8: Save and publish the report to Power BI online services
Step 9: Login to app.powerbi.com with your credentials.
Step 10: Open your report from your workspace where you published.
Embed the Report in SharePoint
So far, we created a report using SharePoint list. Now we are going to embed the report in SharePoint.
Follow the below steps.
Step 1: Open the report in app.powerbi.com
Step 2: If we have O365 E series license then we can use “Embed in SharePoint Online (preview)” option otherwise choose the below option.
Step 3: It will ask you to publish.
Step 4: Finally, it will generate URL and iframe code to place on any web page of SharePoint.
Step 5: Go to SharePoint page and edit where you want to embed this report.
Step 6: Click Insert and choose the Embed Code
Step 7: Copy and paste the iframe
Step 8: Save the page.
Share your comments below.
This article explains that how can we show your Power BI report to everyone.
We have variety options in app.powerbi.com for the specific report.
The notable options are,
Publish to Web
Embed in SharePoint Online (preview)
Export to PowerPoint (preview)
Let us discuss “Publish to Web” Option
Open your Power BI report and click File option. You can see the options as like below.
Choose “Publish to Web” and click “Create embed Code”
Then click “Publish”, it will create a code for us.
We can share the link to anyone or else can embed the iframe in any web page or in SharePoint.
Check the report,
Sachin Tendulkar’s Cricket Data – Embedded Report
Recently, Microsoft released a Technical Preview of PowerBI in SQL Server Reporting services. It has opened a door to create and deploy PowerBI reports within the Reporting services.
This will be a great feature that who are all searching for great report representations in SQL Server Reporting services itself.
This article illustrates, the new look and feel of SQL Server Reporting Services portal and diverse approaches to publish the PowerBI reports.
As we have PowerBI Desktop tool from PowerBI Team, we need to use this tool to develop the PowerBI Reports. It means, we need to use two different tools for developing and publishing reports, i.e. regular SSRS reports from Visual Studio data tools and PowerBI reports from PowerBI Desktop.
Look and Feel
As you know, the look and feel of SQL Server Reporting Services Portal has changed from SQL Server 2016.
In this technical preview, there is a new section added at the bottom to hold all the PowerBI Reports but still we can manage inside our regular report folders.
Approaches to publish PowerBI Reports to the portal,
Develop reports in PowerBI Desktop and perform “Save As” to directly publish (deploy) to portal.
Save the report in local folder and upload manually to the portal.
We discussed about preparing datasets using files from different environment (Local drive & OneDrive) in my previous articles. Go through here.
We have another option under files section which is “SharePoint – Team Sites”.
It is very easy to get a file (dataset) from SharePoint as we are using business email to connect Power BI so no need to provide the credentials again.
Select the file from SharePoint and connect to Power BI. File will be added in to Datasets.
In my last article, we discussed about how to prepare a dataset from local file.
This article speaks about how we can create a dataset which is stored in OneDrive. It can be personal or business OneDrive.
There is no changes up to 4th step from my previous article, after that follow the below steps
As we discussed the levels in Power BI, the first level is creating dataset.
To start prepare the dataset, first sign in to the power BI here,
This article will talk about, how to create a dataset from local file.
Steps to import the local file,