Today we are going to discuss about numerous methods in power query to filter by a list.
Lot to learn, let's dive in.
Source data:
Filtering Criteria:
Extract the Filter Criteria table into power query and convert that into List as shown below.
We will use this list in all our 3 methods.
Method - 1:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Filter = Table.SelectRows( Source, each List.Contains(Lst, [State]))
in
Filter
List. Contains helps us to check, whether the given value exists in list or not.
If it is there, then this function will deliver TRUE else FALSE.
Table.SelectRows function helps us to pick the rows where List.Contains delivers TRUE.
In our case, [State] in each row is the value and "Lst" is the List.
Method - 2:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Filter = Table.SelectRows(Source, each List.PositionOf(Lst, [State]) >= 0)
in
Filter
List. PositionOf function helps us to get the relative position of [State] in the list 'Lst'.
If the [State] not found in the list, then this function delivers -1.
So, we check whether the position is greater than or equal to zero.
Only matching states are filtered, others are omitted.
Method - 3:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Filter = Table.SelectRows ( Source, each not List.IsEmpty( List.FindText(Lst,[State])))
in
Filter
If the state exists in the list, List. FindText function delivers the list with state else it delivers the empty list. If the list is not empty, then we apply the filter for that state.
In all 3 Methods, this is the final output.
We can add or remove criteria in Filter criteria table and all we have to do is to click Refresh All. Now, Final output table is ready with updated filter.
Kommentare