June 2, 2021
I have inherited an old, creaky workbook written in formulas that I would like to build again using Power Query. The workbook runs off a table containing timeliness and quality control data for published documents. I’ve replicated most of the old version, but I can’t get Power Query to do the following. Happy to supply more detail - any help gratefully appreciated!
1. For measuring the speed with which documents are submitted and performance against a 48-hour deadline, I can simply subtract the start time and from the submission time, both in DD/MM/YY HH:MM. However, I need to exclude weekends and UK bank holidays. Is this possible in Power Query?
2. I need to produce a table showing the average hourly error rate for each batch of 10 documents. Is this possible in Power Query. Just to confirm, each row in the source table lists the error data associated with each document.
January 31, 2022
PQ doesn't have a function similar to NETWORKDAYS in Excel. You would have to create your own method. Google for "working days power query" and you'll find many resources that discuss the issue.
If you could upload a file with realistic (though non-confidential information) data and queries, it would help us finding a solution for you.