Scenario:
Suppose, we have a table/dataset called “tbl_SalesDetails”, with Integer Date Key column as “OrderDateKey” as shown below:
In general, we need a Date column in a Dataset to setup the Incremental Refresh in Power BI. However, in this Scenario, we have a Date Key column (OrderDateKey) which is in the Number format.
We can achieve this Scenario, by using a custom Power Query Function to convert Parameter values from DateTime format to Number format as per Source column, as discussed below:
1) Create the RangeStart and RangeEnd Parameters :
The RangeStart and RangeEnd are the required Parameters in DateTime format to be created to setup Incremental refresh in Power BI.
Eg: 2019-01-15 00:00:00 AM/PM (or) 2019/01/15 00:00:00 AM/PM
We need to pass these Parameters as Filters on a Date Key column using Between operator as shown below.
First we pass any available Random values from data as Filters, which will be later replaced with the RangeStart and RangeEnd Parameters.
Next we need to replace those Values with the RangeStart and RangeEnd Parameters :
= Table.SelectRows(vw_Name, each [OrderDateKey] >= RangeStart and [OrderDateKey] < RangeEnd)
Since the RangeStart and RangeEnd parameters are in the DateTime format, and Date Key column in Number Format, we will get the below Type error:
3) Create and Use the Custom function for Type Conversion:
We will create a custom Power Query function with below syntax, which will be further used in the Query to convert the OrderDateKey values from DateTime format to Number.
fn_ConvDateTimeToNum :
= (Val as datetime ) => Date.Year(Val)*10000 + Date.Month(Val)*100 + Date.Day(Val)
When we invoke the function by passing a value as “01/15/2019 12:00:00”, it returns Number format of the DateTime value as 20190115, as shown below:
4) Apply the Function over RangeStart and RangeEnd Parameters for Type Conversion:Finally we can apply the Function over RangeStart and RangeEnd Parameters to convert them from DateTime format to Number format to filter the Data.
= Table.SelectRows(vw_Name, each [OrderDateKey] >= fn_ConvDateTimeToNum (RangeStart) and [OrderDateKey] < fn_ConvDateTimeToNum(RangeEnd))
After applying the Type Conversion, the data will be filtered successfully.
Important Note:
Verify queries have an equal to (=) on either RangeStart or RangeEnd, but not both. If the equal to (=) exists on both parameters, a row could satisfy the conditions for two partitions, which could lead to duplicate data in the model.
For example, = Table.SelectRows(#”Changed Type”, each [OrderDate] >= RangeStart and [OrderDate] <= RangeEnd) could result in duplicate data if there’s an OrderDate that equals both RangeStart and RangeEnd.
The complete Power Query of the Dataset is as follows:
let
Source = Sql.Database(ServerName,DatabaseName),
vw_Name = Source{[Schema=”dbo”,Item=”tbl_SalesDetails”]}[Data],
FilterRows = Table.SelectRows(vw_Name,
each [OrderDateKey] >= fn_ConvDateTimeToNum (RangeStart) and [OrderDateKey] < fn_ConvDateTimeToNum (RangeEnd))
in
FilterRows
Make sure the Query Folding is happening the for the Dataset, by viewing the Native Query:
select [_].[Order_Id],
[_].[Region_Id],
[_].[OrderDateKey],
[_].[Supplier_Id],
[_].[Cust_Id],
[_].[Prod_Id],
[_].[Units_Sold],
[_].[Unit_Price],
[_].[Gross_Sales],
[_].[COGS],
[_].[Disc],
[_].[Net_Sales]
from [dbo].[tbl_SalesDetails] as [_]
where [_].[OrderDateKey] >= 20140115 and [_].[OrderDateKey] < 20161215
Next close and apply the changes to Power Query.
5) Setup the Incremental Refresh for the Dataset/Table in the Power BI Desktop
Right Click and Open the Incremental Refresh settings of the Dataset/Table as shown below.
Next choose the Time Period ( in Years or Quarters or Months or Days ) for Archive and the Time Period
( in Years or Quarters or Months or Days ) for Incremental Refresh.As per the Latest update, with Power BI Premium, we can enable an option to refresh latest data with Direct Query.
It means, it will try to fetch the latest data in Real Time(with Direct Query) instead of Importing the Data like Archive case.
Please refer the latest Microsoft documentation, to learn more on this feature.
Please Note:
Still the Storage Mode for overall Dataset is Import Mode only, as the Direct Query mode will not Support the Incremental refresh as of now:
You can have a Model with Mixed mode, like one Table with Import Mode having Incremental refresh setup, and other table with Direct Query Mode.
However, you need a Power BI Premium Workspace, to Publish such Models to the Service.
5) Publish the Report to Power BI Service (Workspace):
Once we setup the Incremental Refresh in Power BI, we need to Publish the Report to the Power BI Service.
Please note that, we can’t download the Power BI Model file with Incremental refresh enabled.
Hence it is advisable to keep a Backup of your Power BI Report.
The RangeStart and RangeStart are dynamic Parameters, and values for those parameters are dynamically passed by Power BI Engine automatically.
Also note that, we can’t see those Parameters in the Power BI Service like other Parameters:
——————————————————————-
Additional Notes:
To use the Parameters in dataset filters in Power Query, we need to enable the below option from Power BI Desktop > Options > Global > Power Query Editor.
To see the Partitions created behind the Dataset (with Incremental Refresh):
If we are using Power BI Premium workspace, we can connect to Dataset via XMLA Endpoint, using SQL Server Management Studio.
We can get the XMLA End point connection string from Workspace Settings as shown below:
Sample XMLA EndPoint Connection String :
powerbi://api.powerbi.com/v1.0/myorg/[WorkspaceName];Initial Catalog=[PowerBI_Dataset]
In SQL Server Management Studio, We can pass the XML EndPoint Connection string as a Server Name, and then choose Authentication type as “Azure Active Directory – Universal with MFA“, and then enter the User name (eg: username@domain.com) to Connect.
——————————————————————————————————–
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
——————————————————————————————————–
Be the first to comment