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

programsam

Board Regular
Joined
Feb 10, 2016
Messages
123
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?

1704993182454.png
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.
 
Upvote 0
You can filter for sheets with that header specifically using something like:

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

Forum statistics

Threads
1,223,869
Messages
6,175,087
Members
452,611
Latest member
bls2024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top