Forum Discussion

amvans_90's avatar
amvans_90
Regular Visitor
1 year ago
Solved

How to filter to excel sheets by keyword?

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 optio...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    1 year ago

    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

v-hashadapu's avatar
v-hashadapu
Icon for Community Support rankCommunity Support
1 year ago

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.