Forum Discussion
Not all files contains the sheet selected when Transformed and it's giving error
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.
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
2 Replies
jbwtpMemorable Member
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
Thank you 👍
