Get Unique Values in MS Flow from "List Records" in Dynamics 365

[EthanT]

Hello, 

Having a bit of an issue where when I use the "List Records" action in flow, I am retrieving duplicate values.

What I am trying to achieve is the following:

We have a table in D365 called "Business Holidays", these span across multiple countries (8) including the dates and the associated country.

I need to retrieve these holidays to exclude them in a date calculation, hence the issue with the duplicate dates arising. So I was wondering if there was a way to only retrieve unique values?

I am currently setting a variable to the following: "length(body('Retrieve_Business_Holidays_within_"Set_Date"_and_"Due_Date"')?['value'])"

My thoughts are around a function such as "distinct"? but I am unaware of any that achieve this.

I would appreciate any help.

[edgonzales]

@EthanT 

Hi there.  Take a look at the union() function.  It will compare two collections and bring in only the items that exist in both.  By comparing a collection to itself, the result is a list of unique values within that collection.  If you place a Compose action before your count, but after your list, the expression inside might look something like this:

union(body('Retrieve_Business_Holidays_within_"Set_Date"_and_"Due_Date"'),body('Retrieve_Business_Holidays_within_"Set_Date"_and_"Due_Date"'))

I think it's great that you are renaming your action steps, it will definitely make things easier as you work on more complex flows.  I'd suggest avoiding quotes in the action name, though, just to make expressions easier to read, and avoid any possible issues later.

One trick I've used is to use concise names for the action, but then put more descriptive content in the comments.  Totally up to you.

Keep us posted.

-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

[ManishJain]

Hi Ethan,

If we ignore country and do the grouping of date in Apply to each loop by applying filter as per given url 

http://johnliu.net/blog/2018/6/do-group-by-in-flow-with-two-simple-elegant-loops

it might help

Thanks

[EthanT]

@edgonzales, thank you for commenting.

I have tried your proposed solution, however am getting the following result. Perhaps I should have mentioned that the variable that the expression is in is an integer field.

Also regarding your comments around stage descriptions you make a good point and I will consider this moving forward.

[ManishJain]

@EthanT ,

Which solution are you looking at ?

Thanks

[EthanT]

@ManishJain, Thanks for your proposed solution. I will give this a go and let you know the outcome.