Select and Transform sheet in Power Query that has value in Column1

[programsam]

I have a report that comes through weekly that saves to a folder. I access the folder, sort to the most recent and extract data from the newest version of the report. The challenge is that the report always comes in with a few worksheets but the sheets are never named (just "Sheet1", "Sheet2", etc.) and the person creating the report never puts the data on the same sheet. Sometimes the data I need is pasted on "Sheet1", other times it's "Sheet 2" and because it's pulling the sheet from folder, the Transform Sample file always triggers an error when it cannot locate the column "Profit Center" which is a byproduct of the 'Promote Headers' step. It is unable to do this when it tries to load "Sheet1" and the correct data is on "Sheet2". As of now, I'm having to go in and modify the Transform Sample File script to reference the alternate sheet when the error is triggered.

Is there any way to modify the Transform Sample File to parse Column1 on each file and look for the words "Profit Center" and if those words exist, load the sheet but if they do not, to move to the next Sheet?

 

[johnny51981]

I have a similar situation that I run into frequently. How I get around this is I remove all other columns except for the Data column, where your tables are listed. And then AFTER that, expand your table in the Data column.

 

[RoryA]

You can filter for sheets with that header specifically using something like:

= Table.SelectRows(Source, each [Kind] = "Sheet" and Table.HasColumns(Table.PromoteHeaders(_[Data]), "Profit Center"))