Forum Discussion

Fcoatis's avatar
Fcoatis
Post Patron
9 years ago
Solved

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

Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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"

3 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    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"
    • teylyn's avatar
      teylyn
      Advocate III
      8 years ago

      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."

       

       

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