You may already be familiar with the "Get Data" function in Excel. You can use this feature to send data from your worksheet to Power Query. But this only works for defined data ranges; in other words, tables and named ranges only. This is frustrating, especially since Power Query allows you to pull worksheets (not just defined ranges) from other workbooks. But for some reason, it won't allow you to pull data from an entire worksheet from within the same workbook.
I thought I could get around this by temporarily allowing Excel to convert my data into a table, then modify the M code to include the entire worksheet. But alas, there is no parameters under the Excel.CurrentWorkbook() function to pass in that will allow an entire worksheet - only named ranges.
There are a couple things we can do though...
Option 1) Define the entire worksheet as a named range
The problem here is that defining the entire worksheet will cause Power Query to pull in every single column and row within the worksheet - i.e. Columns A through XFD and rows 1 to 1048576. This uses quite a lot of processing power by the vertipaq engine that Power Query uses to store the data.
Option 2) Define the a part of the worksheet as a named range
So defining the entire worksheet, while convenient and quick for humans, is a bit of overkill for Power Query. If you want to go this route, use a named range with the minimum amount of rows and columns needed.
If you have data points all over your worksheet in a bit of a mess, you can "wrap" all these points in a named range. Once everything is selected, you can name it in the upper left hand corner of the spreadsheet. In the example below, I named it "MyData".
Now, because Power Query doesn't have the option to get data from the current worksheet, you need create a blank query first.
Get Data -> From Other Sources -> Blank Query
In the formula bar, type:
= Excel.CurrentWorkbook(){[Name = "MyData"]}[Content]
For those that are curious...
If you are wondering what the stuff after Excel.CurrentWorkbook() is, it's simply a shortcut that M uses. To see more clearly what's happening, try removing {[Name="MyData"]}[Content] from the formula. You'll get a result like this:
We can see that there are two columns; Content and Name, which is exactly what the shortcut above is using. If you had multiple tables and named ranges within your workbook, they would all appear in this table.
Some Potential Problems
An issue arises when you enter data outside of your named range. Once this happens (as it probably will if your data increases on a regular basis), you are forced to adjust the dimensions of your range each time before Power Query can get it. But then again, if that's the case you should consider converting it to a table, where it automatically expands the range for you.
If you'd like to read more on how to create other ranges for use a dynamic parameters in your Power Query models, check out my other article here: Dynamic Power Query Parameters
Alien Tip: If you don't know how to convert your data into a table, it's easiest to highlight any cell within your data, and press "ctrl + l" (lower case L). This will bring up a prompt where you can confirm the dimensions and click okay. The other way is to go to the Insert tab and click "Table".
Thank you for reading - I hope you got something out of this! Let me know in the comments below if you have any other insights you would like to share!
