Power Query - Data filter

[Shaun99]

Good morning.

I hope there may be someone that can shed light on this problem.

I have a dataset that I have filters running with Get and Transform in Excel Data.

The issue I have is that it is not doing what I am expecting it to do.

I have a similar filter that removed certain users from the list from a specific date and includes new users from a specific date without issue, but this one just won't work. I can filter out all of a CentreName, but that is not what I need, it should only filter out these names up to a specific date and there is another filter that filters out different names from this date forward. The date is 7 September 2020. It also does not work.

M Code:

= Table.SelectRows(#"Removed Duplicates", each [CentreName] <> "Gen 1 SD" and [ActionDateTime] < #datetime(2020, 9, 7, 0, 0, 0) or [CentreName] <> "Gen 2 SD" and [ActionDateTime] < #datetime(2020, 9, 7, 0, 0, 0) or [CentreName] <> "Gen 3 SD" and [ActionDateTime] < #datetime(2020, 9, 7, 0, 0, 0))

The Centre name that must be filtered out from this date is "Global", "Enterprise" and "Wholesale" as they are not needed from this date.

Thank you in advance if anyone can guide me please. I'm going to keep looking in the mean time.

[#2]

I figured it out, just needed to group my filters CentreName and ActionDateTime in brackets. Seems to do the trick.

= Table.SelectRows(#"Removed Duplicates", each ([CentreName] <> "Gen 1 SD" and [ActionDateTime] < #datetime(2020, 9, 7, 0, 0, 0)) or ([CentreName] <> "Gen 2 SD" and [ActionDateTime] < #datetime(2020, 9, 7, 0, 0, 0)) or ([CentreName] <> "Gen 3 SD" and [ActionDateTime] < #datetime(2020, 9, 7, 0, 0, 0)))

Thanks for all who had a look.

[#3]

@Shaun99 

I guess you may simplify a bit

= Table.SelectRows(
   #"Removed Duplicates",
   each
      ([ActionDateTime] < #datetime(2020, 9, 7, 0, 0, 0)) and
      (
         ([CentreName] <> "Gen 1 SD")  or
         ([CentreName] <> "Gen 2 SD")  or
         ([CentreName] <> "Gen 3 SD")
      )
)

Corrected one:

= Table.SelectRows(
   #"Removed Duplicates",
   each
      ([ActionDateTime] < #datetime(2020, 9, 7, 0, 0, 0)) and
      (
         ([CentreName] <> "Gen 1 SD")  and
         ([CentreName] <> "Gen 2 SD")  and
         ([CentreName] <> "Gen 3 SD")
      )
)

[#4]

I was actually trying this now, as my solution failed when I changed my source. Also, after the specified date, the CentreName = 3 New names.
So I need to compensate for this as the source has duplicated values before and after the name changes. I was able to get it to work before, but it has failed now. I'm awaiting the source to be fixed, but this is an interim solution which shouldn't make a difference once the source is fixed.
Your solution looks simple, but does not work. I did check my syntax.
Gen SD1-3 change names to Corporate SD, Enterprise SD and Wholesale SD at the specified date and time #datetime(2020, 9, 7, 5, 20, 0)
So I don't want to see the new names before or the old names after.
The logic is so simple, yet I can't get it to work.
Thanks

[#5]

I'm trying this now as an alternative as it has filtered out all my data.
= Table.SelectRows(#"Extracted Date", each [ActionDateTime] < #datetime(2020, 9, 5, 20, 0, 0) and [CentreName] <> "Gen SD1" and [CentreName] <> "Gen SD2" and [CentreName] <> "Gen SD3" or [ActionDateTime] >= #datetime(2020, 9, 5, 20, 0, 0) and [CentreName] <> "Corporate" and [CentreName] <> "Enterprise" and [CentreName] <> "Wholesale")

[#6]

@Shaun99 

You are right, sorry for the mistake, it shall be and in formula, not or

[#7]

@Sergei Baklan Thank you for the tip. Had to figure out the date filtering order otherwise the records would be missing to filter on. I got it to work, and yes had to use date "and" and <> center "or" <> center.
I use a similar filter where staff have been reshuffled.

Ultimately it would be better to do this server side, but for now this is what I have to work with as I am given a flat file.

Thank you.

[#8 · Accepted solution]

@Sergei Baklan 

This is what I have used, and works from what I can see.
= Table.SelectRows(#"Extracted Date", each [ActionDateTime] < #datetime(2020, 9, 5, 20, 0, 0) and [CentreName] <> "Gen SD1" and [CentreName] <> "Gen SD2" and [CentreName] <> "Gen SD3" or [ActionDateTime] >= #datetime(2020, 9, 5, 20, 0, 0) and [CentreName] <> "Corporate" and [CentreName] <> "Enterprise" and [CentreName] <> "Wholesale")