Friday, April 15, 2022

How to reduce Data load by filtering Dataset in Power Query using a Parameter or List of Values

How to use a Parameter or List of Values as Filter in Power Query to reduce the Data Load in Power BI
Scenario:
Suppose we have a very big table for which we want to apply a Filter based on a Column (say Order_Id) while loading in Power Query. This helps in validating the Data before Load and also reduce the load size in Power BI.
Sample Dataset : ( Sales_CurSnapshot )


This load Filter can be done using various methods as discussed below.
1. Parameter Method:
In this Method, we can pass the Parameter as filter to a Column(Order_Id) in Power Query.
This Method is useful when we wants pass a single value as Filter.
Also, once we publish to the Service, we can set the Parameter value to Null to enable the Full load on refresh of Dataset.

First create a Parameter say  "Param_OrderId" with relevant Type and a sample value.


Next Pass the Parameter in to the MQuery Script of the Dataset as highlighted below :
Limit_Load=if Param_OrderId is null then #"Changed Type" else Table.SelectRows (#"Changed Type", each [Order_Id]=Param_OrderId)

let
Source = Excel.Workbook(File.Contents("E:\Tech_Lab\Power BI\DataSets\ ds_Data_Snapshot.xlsx"),null, true),
CurSnapshot_Sheet = Source{[Item="CurSnapshot",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(CurSnapshot_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Order_Id", Int64.Type}, {"Order_Date", type datetime}, {"SalesCountry_Id", Int64.Type}, {"Cust_Id", Int64.Type}, {"Prod_Id", Int64.Type}, {"Units_Sold", Int64.Type}, {"Unit_Price", Int64.Type}, {"Gross_Sales", Int64.Type}, {"COGS", Int64.Type}, {"Snapshot_Date", type date}}),
Limit_Load=if Param_OrderId is null then #"Changed Type" else Table.SelectRows (#"Changed Type", each [Order_Id]=Param_OrderId)
/*Limit_Load = Table.SelectRows(#"Changed Type", each List.Contains(List.Buffer(tbl_Filter), [Order_Id])=true) */
in
Limit_Load

Result :


2. List Method:
In this Method, we create a Table with a single column having List of Values(Order_Ids) and then it can be used as filter in Power Query.
This Method is useful when we want to pass a list of values as Filter.

First create a in Memory table say "tbl_Filter" with a single column as "ref_OrderId" as shown below:
You can convert that as List as well if needed.


Net pass this list of Values to the Power Query as a filter as shown below :

Limit_Load = Table.SelectRows(#"Changed Type", each List.Contains(List.Buffer(tbl_Filter), [Order_Id])=true)

Result :

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog