Forum Discussion
Filter where 'in' query editor
In power quey - is there a way to filter using something like the 'in' clause
Eg instead of
each [SNAPSHOT_DATE_KEY] = 20180916 or [SNAPSHOT_DATE_KEY] = 20180909 or [SNAPSHOT_DATE_KEY] = 20180902
or [SNAPSHOT_DATE_KEY] = 20180826 or [SNAPSHOT_DATE_KEY] = 20180819 or [SNAPSHOT_DATE_KEY] = 20180812 or [SNAPSHOT_DATE_KEY] = 20180805
it would be something like
each [SNAPSHOT_DATE_KEY] in ( 20180916 , 20180909 , 20180902, 20180826 , 20180819 , 20180812 , 20180805)
I would expect something like one of these:
Table.SelectRows(FACT_RES_SUM_AGENT1, each List.Contains({20180916, 20180909, 20180902, 20180826, 201808019, 20180812, 20180805}, [SNAPSHOT_DATE_KEY]) Table.SelectRows(FACT_RES_SUM_AGENT1, each List.Contains({"20180916", "20180909", "20180902", "20180826", "201808019", "20180812", "20180805"}, [SNAPSHOT_DATE_KEY])I've not put in any test data to run the code, so apologies if i've made any keying errors.
3 Replies
I believe the command you are looking for is List.Contains
Could you re-write this step in power query using that?
= Table.SelectRows(FACT_RES_SUM_AGENT1, each [SNAPSHOT_DATE_KEY] = 20180916 or [SNAPSHOT_DATE_KEY] = 20180909 or [SNAPSHOT_DATE_KEY] = 20180902
or [SNAPSHOT_DATE_KEY] = 20180826 or [SNAPSHOT_DATE_KEY] = 20180819 or [SNAPSHOT_DATE_KEY] = 20180812 or [SNAPSHOT_DATE_KEY] = 20180805 )I would expect something like one of these:
Table.SelectRows(FACT_RES_SUM_AGENT1, each List.Contains({20180916, 20180909, 20180902, 20180826, 201808019, 20180812, 20180805}, [SNAPSHOT_DATE_KEY]) Table.SelectRows(FACT_RES_SUM_AGENT1, each List.Contains({"20180916", "20180909", "20180902", "20180826", "201808019", "20180812", "20180805"}, [SNAPSHOT_DATE_KEY])I've not put in any test data to run the code, so apologies if i've made any keying errors.