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.
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 :
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.