Use PQ to find All Errors in a Workbook

I need to be able to count the number of errors I an Excel workbook. Doing this sheet by sheet seems to be easier but I need to know the number of errors in the whole workbook.

It has been suggested that I use PQ for this. With the structure of each sheet n being very different and the number of sheets changing from time to time I want to know how I would do this in PQ.

Thank You

Allister

Hi @AllisterB,

Not a very clear description so I’ll improvise a bit…

I created 3 sample files with a different number of sheets and different used ranges.
Put them in a Folder and used the From Folder option to collect all the errors

Adjusted the “Transform Sample File” query

  1. Stepped back to the Source step, Filtered all Sheets
  2. Removed all Columns except Name & Data
  3. Expanded Data
  4. With Name selected, Unpivoted Other Columns
  5. Kept errors

Next I counted the number of errors in the Result query and turned that back into a table.
With this result
image

Here are my sample files.
ErrorFile1.xlsx (10.3 KB)
ErrorFile2.xlsx (9.5 KB)
ErrorFile3.xlsx (8.8 KB)

eDNA - ErrorCount.pbix (22.9 KB)

I hope this is helpful.

1 Like

Firstly I apologize if my inquiry was not clear.
I am wanting to perform the query on the current workbook rather than a number of files. Other than that the solution sounds ok . What changes do i need to make to the query you provided.

Thank You

Allister

@AllisterB

Unfortunately, Power Query in Excel doesn’t support listing the sheets from within the same workbook. It will only list the tables. You’ll need to do the steps @Melissa has listed above from another workbook.

https://docs.microsoft.com/en-us/powerquery-m/excel-currentworkbook

@AllisterB

I’m going to correct myself here. In Excel PQ you can open the same workbook as if it is another workbook. (Normally I’d do an import from ‘table/range’ which doesn’t support sheets.) This will show the sheets in the query. (https://docs.microsoft.com/en-us/powerquery-m/excel-workbook)

image

or

image

I’ve attached an example of this in action. You just need to amend the steps as needed. i.e. change the source file.

image

Book1.xlsx (19.8 KB)

Hi @AllisterB , we’ve noticed that no response has been received from you since the 17th of July. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!