Forum Discussion

j_ocean's avatar
j_ocean
Helper V
2 years ago

Ingesting Excel Files with a Variable Number of Sheets

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.

Need Help

1 Reply

  • ppm1's avatar
    ppm1
    Solution Sage
    2 years ago

    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