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 :
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.
Limit_Load = Table.SelectRows(#”Changed Type”, each List.Contains(List.Buffer(tbl_Filter), [Order_Id])=true)
Result :
Be the first to comment