Not all files contains the sheet selected when Transformed and it's giving error

[dp1900]

Is there a function or script to check whether a sheet or tab from the multiple spreadsheets that is being combined exists? The reason is after selecting a specific tab as source and performed a combine/transform, some files doesn't have the specific tab/resource specified and therefore giving me an error and it stops there.

Example:

= Source{[Item="Transaction",Kind="Sheet"]}[Data]

The 'Transaction' sheet doesn't exist on the next file being transfomed and therefore the query gives an error and won't proceed on the rest of the files.  Can someone help? Thanks in advance.

[dp1900]

Thank you 👍

[jbwtp · Accepted solution]

Hi @dp1900,

Technically, there is a flag on the import wizard form (bottom-lef corner):

It generates a code to skip files with errors (including missing tabs) by adding something like this:

= Table.RemoveRowsWithErrors(#"Removed Other Columns1", {"Transform File"})

Or you can transform the helper function to something like this, if you want to have more control (e.g. conditionally substitute tab names orsomething alike):

let
    Source = Excel.Workbook(Parameter1, null, true),
    Check = List.Contains(Source[Name], "P&L")
in
    if Check then Source{[Item="P&L",Kind="Sheet"]}[Data] else #table({},{})

Cheers,

John