How to load Excel raw data into power query without converting into data table?

Question

I am trying to find the name and full path of the current excel file where the power query is run. I dont need the filename as such, its just that I want to have access to a sheet which do not have any data table, rather raw data is there.

When I try the Excel.CurrentWorkbook() it only gives a list of tables in the current workbook. But when I try to access the file using its name and full path using File.Contents() then all the sheet objects are returned which includes the sheets that contain raw data (without being converted into a data table).

So my plan is, if I could get the file name and path of the current workbook, then I can use it to access the sheet. I cant hardcode the file name as it gets changed everyday with the date as suffix.

Is there any other way around it?

Answer 1

I don't think this is currently possible using Excel.CurrentWorkbook().

It's possible to use a substring of CELL("filename") as a named range to read in the current path and workbook name into Power Query to use File.Contents but at that point, it's probably easier just to convert the sheet to a named range instead (only a few keys/clicks: select all data and hit the From Table button in the Data tab Get & Transform ribbon section).