Forum Discussion

GK999's avatar
GK999
New Member
2 years ago

only load worksheets from workbook containing certain text

Does anyone know how I can only load worksheets from an excel workbook (that i manually select in get data), only if the worksheets contain the words "benefits" or "Deceased" or "Voters Roll" etc? 

 

I've looked all over youtube and used Copilot but still have an error.  it has to be dynamically built in the code, as another user will use it as a template.  LoadsheetBenefits is the name of the custom function I created.  Something is wrong with "sheets".  Thank you in advance for any help.

 

I have written a custom function:  but I'm getting the error message:  An error occurred in the ‘LoadSheetBenefits’ query. Expression.Error: The column 'Sheet' of the table wasn't found.
Details:
Sheet

Custom function is as follows.>>

 

let
LoadSheetBenefits = (filePath as text) =>
let
Source = Excel.Workbook(File.Contents(filePath), null, true),
FilteredSheets = Table.SelectRows(Source[Sheet], each Text.Contains([Name], "benefits")),
Data = Source{[Item=FilteredSheets{0}[Name], Kind="Sheet"]}[Data]
in
Data
in
LoadSheetBenefits

Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    GK999 Try this:

    let
LoadSheetBenefits = (filePath as text) =>
let
Source = Excel.Workbook(File.Contents(filePath), null, true),
FilteredSheets = Table.SelectRows(Source, each Text.Contains([Name], "benefits")),
Data = Source{[Item=FilteredSheets{0}[Name], Kind="Sheet"]}[Data]
in
Data
in
LoadSheetBenefits
  • GK999's avatar
    GK999
    New Member
    2 years ago

    Hi Greg, thank you for your reply, but this is the code in my custom function from my answer, I am still getting the error message as above.