Getting data from current workbook with Power Query | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★

★ Check out the blog post★

★ About this video ★
Computers and humans think about data differently. People like to pre-categorize data into different areas. For example, we might keep January, February, and March data on separate sheets. However, computers are more efficient when data with the same structure is held in one table, with a separate column to identify each month. Power Query is amazing at getting data from the current workbook, which is in a human-understandable format, and transforming it into a computer-optimized format.

0:00 Introduction
0:34 Get data from Table/Range
1:44 Get data from current workbook
4:27 Filter out the query table
6:55 Conclusion

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[paulmartinlife]

An excellent description for a common task. Many thanks from an Aussie living in Warwickshire.

[kebincui]

I learnt the caveat when using Excel.CurrentWorkbook() function. Thanks Mark for sharing your always excellent tutorial👍👍

[IvanCortinas_ES]

As always a great explanation. Thank you Mark!!!

[lapaleves]

well it sorta works by pointing to yourself: Source = Excel.Workbook(File.Contents("C:\Book1.xlsx"), null, true){[Item="Sheet1"]}[Data], but you have to save in between. but why would i want this? i wanted to have a fully blank sheet to paste randomly filled cells _anywhere_ to make a single column unique list of them. InferSheetDimensions = "true" helps avoiding defining max columns and rows. it almost works with a single cell table in the corner as that resizes upon paste, and InferSheetDimensions then cuts all empty (copied) rows even before loading in query. the problem is if the user pastes anywhere else than A2... which is something i really wanted this to be resistent against. Doesn't really matter as long as no useful way making the filepath dynamic (can''t send to a 1.0 user for simply save-paste-refresh). =CELL(... method is unsafe as it updates to the last opened workbook...

[saikrishnapagadala]

Hey Mark!

Awesome video. Quick doubt - we can also use combine queries (append) method to do this similar task, correct? Per my understanding, we are combining tables here in this video and for us to append, we should make the table as a query (connection).

Which method is the more effective one; combining tables using a blank query (Excel.CurrentWorkbook) or Append queries?

[sheauwenchong]

If I Get Data>From File>From Excel Workbook then point to the current file and transform my tables from there, versus using the Excel.CurrentWorkbook function, will there be any impact on performance like slow refresh?