Hi,
I was amazed by the M code Excel.CurrentWorkbook( ) that allow us to append several worksheets easily, without using Append query in PQ editor
Is there any equivalent M code for bringing in worksheets from various sources ( D drive, server drive) into Power Query, without bringing in the physical tables ( or minimally) into one single query just like Excel.CurrentWorkbook( )
for e.g.
I open a new workbook, open a blank query at formula bar key in equivalent M code to bring in PC_1 ad PC_2 tables from the attach
Thank you !
Hi Chris,
Not sure what you want.
D Drive I guess is a local drive?
You can use File.Contents(FilePath) , this will return file data in binary format, then you can add a new column with the formula =Excel.Workbook([Content]) to extract data from the file.
Hi Catalin,
I try to explain,,
in my attached example, because the source file is in the excel itself, I can use =Excel.CurrentWorkbook( ) in the blank query and that it
what if I open up a blank excel workbook, open a blank query, you mean I can use =File.Contents( d:tempfile.xlsx) to import in ?
Hi Catalin, basically I want to just issue a M code that will consolidate all the worksheets like what Excel.CurrentWorkbook( ) did, i.e. instead of import physically to PQ and then use Append query to combine them
Is this possible ?
Hi Chris,
Attached is an example, you can use that, or start with a blank query with this formula:
= Excel.Workbook(File.Contents("C:UsersCatalinDesktopNew folderSourceTable.xlsx"), null, true)
This will give you the list of sheets in that workbook, you can select the ones you want and expand the data table.
As mentioned, I only used File.Contents() and Excel.Workbook() to extract data from the file.
Nothing is stopping you from trying, I think trying things is the best way to go, even if it fails sometimes.
Hi Catalin, perfect ! this is exactly what I am looking for, however some users still like to import all the tabs into PQ and append it
so I thought by just writing this short code ( thanks to you !!)
= Excel.Workbook(File.Contents("C:UsersJO_HOMEDownloadstempExcelCurrentWorkbook.xlsx"), null, true)
it is clean, light and display what I want, the master combined dataset
Chris Yap
