Forum Discussion
loop function to get free dates
Hi ANBILY1 ,
Please refere below output snap and PBIX file.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Dear, follow the information as requested:
| Auditor | State |
| B94V | SE |
| DPEW | SP |
| DQMW | SP |
| EMJJ | SP |
| F5CD | RJ |
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.
| Supplier | State |
| 10006087 | RJ |
| 10007454 | SP |
| 10008452 | PR |
| 10022612 | BA |
| 10025681 | SP |
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.
| Date |
| 03/09/2025 |
| 04/09/2025 |
| 05/09/2025 |
| 06/09/2025 |
| 07/09/2025 |
| 08/09/2025 |
| 09/09/2025 |
| 10/09/2025 |
The table above contains data corresponding to dates from today to one year in the future, that is, today plus
364 days. Each auditor will be entered in a column, and their respective audits will be filled in with the code
of the supplier whose audit is scheduled.
| Auditor | Municipal holiday | State |
| B94V | 08/07/2026 | SE |
| B94V | 08/12/2025 | SE |
| B94V | 17/03/2026 | SE |
| B94V | 24/06/2026 | SE |
| DPEW | 09/07/2026 | SP |
| DPEW | 28/02/2026 | SP |
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.
| Date | National holiday | Municipal Holiday | DPEW | DQMW | EMJJ | F5CD | FQ3C | FQR6 | FWXE | G1TD | G323 | G495 |
| 03/09/2025 | 10006087 | |||||||||||
| 04/09/2025 | 10006087 | |||||||||||
| 05/09/2025 | 10006087 | |||||||||||
| 06/09/2025 | 10006087 | |||||||||||
| 07/09/2025 | Holiday | 10006087 | 10007454 | |||||||||
| 08/09/2025 | Holiday | 10007454 | ||||||||||
| 09/09/2025 | 10007454 | |||||||||||
| 10/09/2025 | 10007454 | 10008452 | ||||||||||
| 11/09/2025 | 10007454 | 10008452 | ||||||||||
| 12/09/2025 | 10008452 | |||||||||||
| 13/09/2025 | 10008452 | |||||||||||
| 14/09/2025 | 10008452 | |||||||||||
| 15/09/2025 | ||||||||||||
| 16/09/2025 | ||||||||||||
| 17/09/2025 |
Above is a sample of the results. In other words, the results table allows you to see which suppliers have scheduled audits, which auditors will conduct the audit, and the auditors' free dates for scheduling future audits.
The goal is to ensure that no supplier in the supplier list is left without a scheduled audit.
Thank you in advance for your help and attention.
HI ANBILY1 ,
Thank you for providing sample data. Please refer below output snap and attached PBIX file.
I have created M code . Please refer the Query editor.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi ANBILY1 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Dear Sir, as per the example below, I need to group by 5 blank spaces in the value column in
relation to the date and code column, that is, 5 consecutive dates from the most recent onwards
that have the same code.I need to do it using power query, that is, with M language.This is how it is now
This is how I need it to stayThe dates column must contain the list with the 5 dates (I will expand it later), if there is no code with five dates, for example if I have 12 lines I would have two groups of 5 and 1 with 2 then I filter and leave only those that have 5. The important thing is to separate as many as possible by group of 5.
Thank you in advance for your attention.
Hi ANBILY1 ,
Please refere below output snap and PBIX file.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh