Oct 05 2020 12:50 AM
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.
Oct 05 2020 02:30 AM
Oct 05 2020 07:49 AM - edited Oct 05 2020 01:26 PM
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")
)
)
Oct 05 2020 08:35 AM
Oct 05 2020 08:55 AM
Oct 05 2020 01:25 PM
You are right, sorry for the mistake, it shall be and in formula, not or
Oct 05 2020 10:03 PM
@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.
Oct 05 2020 10:13 PM
SolutionThis 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")
Oct 05 2020 10:13 PM
SolutionThis 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")