Forum Discussion
loop function to get free dates
Hi ANBILY1 ,
I am including .pbix file for your better understanding, please have a look into it:
Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
I am not sure you will be able to do 100% of what you need within Power Query, but here is an example in the attached pbix file of what might be possible using your example data. (Note, I moved the municipal holiday by a couple of days just for the example.)
Schedule remains as your example data.
Auditor table as follows...
Supplier table as follows...
Results of a Supplier audit schedule...
Final Schedule result...
Hope this gets you pointed in the right direction.
Congratulations, it really helped a lot.
Only the municipal holiday section should be tailored to each auditor's state of residence, meaning it only counts as a holiday for the auditor who lives in that state. But the auditor's table example I sent didn't show this.
Above is the table with the auditors and their respective municipal holidays, that is, it may be a municipal
holiday for a certain auditor and not for another.
Your feedback was really impressive. Thank you very much.
Here is a new version that uses the concept of Auditor Holiday table. (I added a line to your example data to test the functionality against the existing example data.)
Hi ANBILY1 ,
Thanks for reaching out to the Microsoft fabric community forum.I would also take a moment to thank jgeddes , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.Best Regards,
Community Support TeamHi ANBILY1 ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.Best Regards,
Community Support Team
Dear Sir, the solution presented was truly exceptional. Since the process of selecting auditing teams is quite complex, I do have another question. It's
this: if an auditor from the same state as the supplier already has an audit scheduled, meaning
their schedule is full, and the auditor from another state doesn't have an audit, then the auditor
from another state will respond, meaning they will perform the audit.
In other words, the rule is for the auditing team to be from the same state as the supplier, but if
the auditor from the same state doesn't have an available date and the auditor from another state
does, they can perform the
audit. Always based on the most recent free date.
Thank You.Dear Sir, thank you again for your help. However, the date range doesn't have to be sequential; it can start when there's a free date and
continue until there are 5. For example, if the first free date is October 3, 2025, and it's on a
Friday, you can continue on the second if there's a free date. This would then be completed with
October 6, 7, 8, and 9, 2025. In short, it's a 5-day sequence, but it can be interspersed with Saturdays, Sundays, or holidays,
as long as there are 5 business days. Thank you for your attention.Hi ANBILY1 ,
I am including .pbix file for your better understanding, please have a look into it:
Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.