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 duplicatessome 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

​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 duplicatessome 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 workbookQ2 – how do I get Folder.File to open one sheet of a workbook thanks! mm    Read More

​