How to filter to excel sheets by keyword?

[amvans_90]

When connecting to an excel workbook, how do I filter to multiple tabs (worksheets) by keyword? I think I used to be able to expand the content column and use the text contains filter, but that option is no longer there.  In excel, the users create a new tab for each year they collect the data and I don't want to have to manually add tabs each year.  All the column names are the same. I only want to include the tabs labeled "Events"

[v-hashadapu]

Hi @amvans_90 , I hope you're doing well! Just checking in to see if you had a chance to review the details shared earlier. If any of the information addressed your needs, feel free to mark it as "Accept as Solution" or give it a 'Kudos' to help others in the community. Please let me know if you have any further questions!

[amvans_90]

Hi @v-hashadapu , unfortunetly, I could not make either suggestion work.  I think because my excel workbooks are being pulled from a SharePoint site, instead of being a local file.  We're having to revise the workbooks to a standardized template anyways since a few of them were formatted differently. I'll revisit these solutions once the users have converted to the new template. Thank you for your help!

[v-hashadapu]

Hi @amvans_90 , Thanks for the update, Please try the suggestions once your files are standardized and share your thoughts. 
Thank you.

[v-hashadapu]

Hi @amvans_90 ,
I hope the information shared was helpful. If you have any additional questions or would like to explore the topic further, feel free to reach out. If any of the responses resolved your issue, please mark it "Accept as solution" and give it a 'Kudos' to support other members in the community.
Thank you!

[v-hashadapu]

Hi @amvans_90 ,
I hope the information shared was helpful. If you have any additional questions or would like to explore the topic further, feel free to reach out. If any of the responses resolved your issue, please mark it "Accept as solution" and give it a 'Kudos' to support other members in the community.
Thank you!

[v-hashadapu]

Hi @amvans_90 , Thank you for reaching out to the Microsoft Community Forum.

After you’ve selected the Excel file and clicked Transform Data, you’ll be in the Power Query Editor. There, go to the Home tab and click Advanced Editor. Replace the code with this:

let

    Source = Excel.Workbook(File.Contents("C:\Path\To\YourFile.xlsx"), null, true),

    FilteredSheets = Table.SelectRows(Source, each Text.Contains(Text.Lower([Name]), "events")),

    Promoted = Table.TransformColumns(FilteredSheets, {"Data", each Table.PromoteHeaders(_, [IgnoreErrors=true])}),

    Combined = Table.Combine(Promoted[Data])

in

    Combined

If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[Ritaf1983]

Hi @amvans_90 
To achieve a wanted result you can follow thius steps :
1. import excel file

2. select obe of the sheets + transform

3. go to Sorurce step :

4. At this stage you can filter sheets name

M code:

let
    Source = Excel.Workbook(File.Contents("YourFile.xlsx"), null, true),
    FilteredSheets = Table.SelectRows(Source, each Text.Contains([Name], "Events")),
    Combined = Table.Combine(FilteredSheets[Data])
in
    Combined

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ashish_Excel]

Hi,

If you follow the steps shown here, then should  (step 4 or 5) see a list of all tabs which you can filter.