Ingesting Excel Files with a Variable Number of Sheets

[j_ocean]

I have a Dataflow ingesting/combining a folder full of excel files from sharepoint. The files are dropped by another user, and sometimes its a single sheet but sometimes its two sheets...with the data on the second one.

I know I can ingest using the sheet index e.g., a single sheet file is index 0, and not have to worry about sheet names. The problem is I would sometimes need index 0 and sometimes index 1.

Is there a way to check, file-by-file, how many sheets there are and adjust the sheet index accordingly?

I can think of several workarounds outside PQ but as a rule I'd like to engineer around this sort of thing.

[ppm1]

Two potential approaches:

1. use try ... otherwise - try on index 1 and do index 0 in the otherwise

2. keep them both and then use the max row for each workbook with Table.Max

Pat