Forum Discussion

michaelmench's avatar
michaelmench
Copper Contributor
Aug 10, 2024

PowerQuery Question: = Folder.Files() opens all sheets in Workbooks

I’m automating a PowerQuery to open monthly reports. 

  • blank query for “FolderLocation” where I store new 
  • = Folder.Files(FolderLocation) to open them (but I only want ONE sheet not all sheets)
  • then, Add Column/Custom Column/
  • = Excel.Workbook([Custom], true)

 

That converts them all to tables for additional use… but… weird stuff happens.

 

  • some of the reports are already tables and I get duplicates
  • some of the reports are sheets not tables and THOSE workbooks have multiple sheets.  One sheet is the data I want and the other sheets are pivot or formula tables.

I just want to bring in the data sheet.  Since I’m automating it, I’m happy to spend the time now typing in the name of the sheet if there is a formula for that.

 

Q1 - how do I keep from getting duplicate entries opening a single table workbook

Q2 - how do I get Folder.File to open one sheet of a workbook

 

thanks! 

mm

 

 

  • michaelmench's avatar
    michaelmench
    Copper Contributor
    I think I figured it out! During one of my steps, I just filter the column to eliminate the ones I don’t want.

    -mm

Resources