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

--------------------------------------------------------------------------------------------------------