Log In
Register
Home
August 21, 2019
Offline
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 !
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
August 21, 2019
Offline
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:\temp\file.xlsx) to import in ?
August 21, 2019
Offline
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 ?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Chris,
Attached is an example, you can use that, or start with a blank query with this formula:
= Excel.Workbook(File.Contents("C:\Users\Catalin\Desktop\New folder\SourceTable.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.
August 21, 2019
Offline
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:\Users\JO_HOME\Downloads\temp\ExcelCurrentWorkbook.xlsx"), null, true)
it is clean, light and display what I want, the master combined dataset
Chris Yap
1 Guest(s)
