loop function to get free dates

[ANBILY1]

Dear all, thank you in advance for your collaboration.
I have the following question:
I have two tables, one for suppliers and one for auditors.
In the supplier table, I have the following columns: supplier and ranking to determine their position for audit.
In the auditor table, I have a column with the date one year in the future, that is, from today to 365 days, and 18 more columns corresponding to each auditor. I need to select a pair of auditors for each supplier using both tables that meet the following rules:
If auditor A and auditor B have 5 free days and the state is the same between the auditors, and the supplier returns Yes; otherwise, it returns No;
Return the smallest range above, that is, the date that starts first and has 5 days;
After the previous steps are met, the auditors are placed on reserve and the respective range is unavailable.
Any contribution is welcome.

[jgeddes]

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.

[ANBILY1]

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.

[jgeddes]

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.)

[v-menakakota]

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 Team 

[v-menakakota]

Hi @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  

[MarkLaf]

Please provide some sample data representing your problem set. Pasted in such that we can easily copy and paste into Power BI or Excel. Also, a snip or paste of the expected result.

A few clarifying questions, which probably would be answered with some sample data:

1. Do all auditors start out with totally open dates? Or are there random reservations (scheduled vacation or something)?
2. Can you define what, "state is the same between the auditors" means? Does this just mean they both have unreserved dates? Or is there a third auditor dimension that holds auditor locations (in case you means states like FL, NY, CO)?

[ANBILY1]

Dear Sir, thank you for your interest in helping.

I am translating the text from Portuguese to English.

Regarding your questions: Answer 1 - Yes, it is necessary to remove weekends and holidays. The auditors' table 
already has holiday columns. Municipal holidays already count as a busy date for each auditor residing in the 
municipality with the holiday. National holidays, on the other hand, have a column indicating that their 
respective dates cannot be used for auditing.

2 - State is the geographic location that must match. There is another table with the auditor's domicile 

Above is a sample of the auditor schedule table, following the table logic:
- The date column is from today plus 364 days, that is, one year in the future;
- It has the holiday columns;
- The other columns are the codes representing each auditor. The header is the auditor's code, and in the rows, 
the code repeats, indicating that they already have an audit scheduled. The empty spaces are the dates available 
for new audits. That is, auditors who have coinciding free dates and the same domicile and state of residence are
authorized to audit the supplier, and so on.
- After verifying that the authorized pair is available, complete the table and search for the next pair until 
the list of suppliers is exhausted.

Above is the supplier table. The supplier is represented by a code and has a state that must be the same as the state of the pair of auditors.

Above is the table with the auditors column, with each code in each row representing an auditor. The other column is the auditor's state of residence, which must be the same for both auditors in the pair and the same for the supplier.

I know this is a complex issue, thanks again for your help.