Use PQ to find All Errors in a Workbook

Power Query
1

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

2

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

image
image719Ɨ209 13.2 KB

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
3

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

4

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

5

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

6

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!