Second filter criteria independent of First in Data Source

[Anonymous]

Hello,

I'm trying to limit the load/Refresh time of the data by adding date filters in the source table. Problem is the filters are dependent of each other. i.e. First Filter is limiting the data set and second filter is further limiting the data set which was left after applying first filter (that's how it's suppose to work I guess).

My question is; Is there a way for me to apply filters on individual column independent of the previous filters?

Thanks.

[Jimmy801]

Hello @Anonymous 

could you please explain your exact filter criteria?

With the function Table.SelectRows you can really build up big filters with multiple checks.

Jimmy

[Anonymous]

@Jimmy801 Table.select rows is dependent of the first filter, following is the query which is not working for me:

#"Filtered Rows" = Table.SelectRows(#"Renamed Columns5", each [Column B Date Stamp] > #date(2019, 1, 1)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Column C Date Stamp] > #date(2019, 1, 1))

The filter criteria are different date stamps. For e.g.:

Column A         Column B         Column C         Column D
Name 1            9/11/2019 

Name 2                                     3/11/2019         4/9/2019 
Name 3                                     4/3/2019           4/10/2019 
Name 4                                     6/25/2016         8/11/2019 
Name 5            3/19/2019        4/9/2015 

I would like to source the data where If I filter Column B by "After 1/1/2019" and it gives me Name 1 and Name 5; and at the same time I'd like to use the filter on Column C "After 1/1/2019" where I get Name 2 and Name 3.

Right now when I'm using the filter on column B it's deleting the data from other columns too

[Jimmy801 · Accepted solution]

Hello @Anonymous 

then just apply a "or" in your select.rows-function

Here the complete solution

let
	Source = #table
	(
		{"Column A","Column B","Column C","Column D"},
		{
			{"Name 1","43719","",""},	{"Name 2","","43535","43564"},	{"Name 3","","43558","43565"},	{"Name 4","","42546","43688"},	{"Name 5","43543","42103",""}

					}
	),
    ToDate = Table.TransformColumns
    (
        Source,
        {
            {
                "Column B",
                each Date.From(Number.From(_)),
                type date
            },
            {
                "Column C",
                each Date.From(Number.From(_)),
                type date
            },
            {
                "Column D",
                each Date.From(Number.From(_)),
                type date
            }
        }
    ),
    Filter = Table.SelectRows
    (
        ToDate,
        each [Column B]>#date(2019,1,1) or [Column C]>#date(2019,1,1) 
    )
in
	Filter

Here the core code for doing your filtering

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

@Jimmy801 @v-lid-msft  Thanks guys, such a simple fix

[v-lid-msft]

Hi @Anonymous ,

We can use "or" in Table.SelectRows function to meet your requirement:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUwVNJRMjIwtNS31DcEsUEoVgcqaQQRAMsbQ+TBbBN9S4QiYxRFJgg1hgYIRSZwRWb6ZvpGpjBVFiBT4apMkSyzhLBNwZaBHBULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t, #"Column D" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type date}, {"Column C", type date}, {"Column D", type date}}),
    FilterTable = Table.SelectRows(#"Changed Type", each [Column B] > #date(2019,1,1) or [Column C] >#date(2019,1,1))
in
    FilterTable

Best regards,