Excel.CurrentWorkbook

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Excel.CurrentWorkbook

What is Excel.CurrentWorkbook?

Excel.CurrentWorkbook is a function in Power Query M that allows you to reference tables and named ranges within the current workbook. This function is particularly useful when you have a workbook with multiple tables and you want to extract data from them into a single query.

How to use Excel.CurrentWorkbook

To use Excel.CurrentWorkbook, you need to understand its syntax. The syntax for this function is as follows:

Excel.CurrentWorkbook()

This function returns a record containing all the tables and named ranges in the current workbook. You can reference this record to extract data from specific tables or named ranges.

Here's an example of how to use Excel.CurrentWorkbook to extract data from a table:

let

Source = Excel.CurrentWorkbook(),

Table1 = Source{[Name=”Table1″]}[Content]

in

Table1

In this example, we first use Excel.CurrentWorkbook to get a record of all the tables and named ranges in the current workbook. We then reference the "Table1" table from this record and extract its content.

The M code behind Excel.CurrentWorkbook

The M code behind Excel.CurrentWorkbook is quite simple. Here's what it looks like:

let

Source = Excel.Workbook(File.Contents(_), null, true),

CurrentWorkbook = Source{[Name=Excel.CurrentWorkbook]}[Content]

in

CurrentWorkbook

Let's break this code down:

- The first line of the code defines a variable called "Source" and sets it to the Excel workbook that is currently being used in Power Query. The "File.Contents(_)" function is used to get the contents of the workbook.

- The second line of the code defines a variable called "CurrentWorkbook" and sets it to the content of the table or named range specified by Excel.CurrentWorkbook.

That's it! The M code behind Excel.CurrentWorkbook is simply a reference to the content of the table or named range specified by the function.

Tips for using Excel.CurrentWorkbook

Here are some tips for using Excel.CurrentWorkbook effectively:

- Use descriptive names for your tables and named ranges. This will make it easier to reference them in your Power Query M code.

- If you have a large workbook with many tables and named ranges, consider organizing them into separate sheets or workbooks. This will make it easier to manage your data and reduce the complexity of your Power Query M code.

- Be careful when using Excel.CurrentWorkbook in conjunction with other functions that reference external data sources. If the external data source is not available, your query may fail to refresh.

Excel.CurrentWorkbook is a powerful function in Power Query M that allows you to extract data from tables and named ranges within the current workbook. The M code behind this function is quite simple and can be easily customized to suit your specific needs. By following the tips outlined in this article, you can use Excel.CurrentWorkbook effectively and efficiently in your Power Query M code.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)