Power Query - Finding source of errors when using functions

[gazpage]

I've got a file with several hundred tabs with identical structure that I needed to transform and bring in to one table. I created a query on one sheet, converted it to a function and applied it to a list of the sheet names. All worked fine so far.

I then received an updated version of the source sheet and reconnected my query. However this time I got an unable to format as a number type error. I eventually tracked it down to an entry that contained both text and numbers "16 hours per day", but it took me ages. The error was therefore generated by the function for one of the sheets and so as soon as I expanded the columns I get the error status with no indication of where it came from.

Any better way to do this?

 

[Matt Allington]

You could try adding an id column in the function. Then when it fails you will see the last one that worked and can narrow it down from there.

 

[gazpage]

Apologies, but I'm not quite sure I follow. Happy to try something to see if it works but don't quite understand what you are suggesting.

 

[Matt Allington]

sorry. Edit the function and add a new last step to be "Add column, Index Column". the function will then return a unique ID for each line as part of the output. run the query and then check which line the function fails on (presumably the last line returned). From there you should be able to work out which sheet failed by counting the rows on each sheet until you find the right one.