One of my recent integrations wasn’t working correctly. My customer complained about missing records. So, I went to investigate, and this is what I discovered.
I have sample contacts in my Dynamics 365 Sales.
The filter is super simple – contacts with Created On field Last 2 Years. The number of records 135.
Now, let’s pull the same records from Dataverse using dataflow.
Let’s apply the filter. There is no Last 2 Years. The closest I could get is this one.
#”Filtered rows” = Table.SelectRows(#”Reordered columns”, each Date.IsInPreviousNYears([createdon], 2))
Let’s see how it works!
Ooops, it doesn’t! We see how number of records reduced dramatically ☹
I wonder why. Oh, this is why.
https://learn.microsoft.com/en-us/powerquery-m/date-isinpreviousnyears
“Indicates whether the given datetime value dateTime occurs during the previous number of years, as determined by the current date and time on the system. Note that this function will return false when passed a value that occurs within the current year.”
So how do I get it working then?! Let’s try something clever!
https://learn.microsoft.com/en-us/powerquery-m/date-isinpreviousnmonths
“Indicates whether the given datetime value dateTime occurs during the previous number of months, as determined by the current date and time on the system. Note that this function will return false when passed a value that occurs within the current month.”
#”Filtered rows” = Table.SelectRows(#”Reordered columns”, each Date.IsInPreviousNMonths([createdon], 24))
It’s working. Kinda…
Now when we know we can’t “un-know” it. It’s still obviously incorrect.
Let’s do Dynamics 365 first. John New is a contact created this month and still appear in the view with the filter we set up before.
What about Power Query?
We see – John didn’t make it here.
Let’s adjust the formula again.
Hello, John!
Is there easier way to do it? Possibly.
I just wanted to point to the fact that there no exactly matching function in M I know to work the way we have in Dynamics and Dataverse and we have to be careful while working with the DataTime functions.
It’s always DateTime, isn’t it? 😊
(This article was created by a human. The information in the article is based on the author’s real-life consulting experience and reflects the author’s perspective on aspects of life and technology: subjective, unfiltered, and genuine.)