Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Fcoatis
Post Patron
Post Patron

Power Query extract specific sheets of the same Excel Workbook

Hello community,

 

Is there a way to use Power Query to extract only data from the same workbook where all the sheets  names  begin with "rec_" for instance?

 

Thanks in advance

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

Sure:

 

let
    Source = Excel.Workbook(File.Contents("<YourPathAndFileName"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each [Kind] = "Sheet" and Text.StartsWith([Item], "rec_"))
in
    #"Filtered Rows"
Specializing in Power Query Formula Language (M)

View solution in original post

3 REPLIES 3
MarcelBeug
Community Champion
Community Champion

Sure:

 

let
    Source = Excel.Workbook(File.Contents("<YourPathAndFileName"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each [Kind] = "Sheet" and Text.StartsWith([Item], "rec_"))
in
    #"Filtered Rows"
Specializing in Power Query Formula Language (M)

Hello,

 

I find that this does not work if the file lives in a OneDrive folder synced to the PC. I works fine with the path

 

= Excel.Workbook(File.Contents("C:\stuff\Book1.xlsx"), null, true)

 

but with the path C:\Users\user\OneDrive\Forums\filename.xlsx it throws an error "The process cannot access the file because it is being used by another process."

 

OneDriveQuery.png

 

How can it be made to work for OneDrive stored files?

Thank you

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.