When using Power Query to connect to an External Workbook you can easily reference the sheet names and import / combine data from those sheets. Also combining multiple Tables and Ranges is simpler.
It is currently not possible to do the same thing in the same workbook as the query you are writing.
For example I want to consolidate the data from sheets 1, 2 and 3 and load the output into a table on Sheet 4 of the same file.

Workaround currently is to connect to the same file but this comes with issues around having to re-save before refreshing and having to edit source steps when the file is renamed or moved

When Dynamic Arrays (DA) were introduced, it opened a plethora of possibilities and a lot of formula nerds and automation ninjas were empowered to begin a new wave of creativity while building more powerful worksheets with fewer formulas. As we explore the potential, we are discovering that there is a new limitation that begs for a solution. This is a proposal for a new function, SEGMENT, that can expand the flexibility of scalar functions that accept arrays as parameters so they can operate more robustly in an array world.

Consider the following problem as an example.

We have a two-dimension table of numeric values – we'll call each number a score. The values are organized by row – one row for each student. Each of 20 students has 10 scores, and we want to find the maximum of the averages of the top five scores for each student. We're going to need to use the LARGE, AVERAGE, and MAX functions.

Without getting into arrays, we create five "helper" columns and, in each, put our LARGE function. For the first student, the formula in the first helper column is:

=LARGE(A1:J1,1)

The second helper column:

=LARGE(A1:J1,2)

And so on. We average these in a sixth column and then get the maximum of the averages for all 20 students. Six additional columns and a single cell dedicated to the maximum of the averages across all students.

We can introduce arrays and cut our six helper columns down to one by coercing the LARGE function to produce an array and passing that array to the AVERAGE function, one formula per student (row):

=AVERAGE(LARGE(A1:J1,{1,2,3,4,5}))

Now we have one helper column and a cell dedicated to the maximum of the averages.

Can we go further in our effort to produce a solution with a minimal number of formulas? Given the current state of things, that is as simple as I can go. There may be some magic out there that can take this further but I'm at a loss at this point.

The problem with going further with arrays is we are bound by the inability of Excel to know that, if we pass our entire array containing all students and their scores to LARGE, we want it to process each student separately and generate one result per student. Or, in this case, one result per student and for each of the five ranks we passed to it. This is because the LARGE function along with many other functions that accept arrays as parameters handle the arrays internally. Whereas any function that accepts a scalar value can be coerced to produce an array – not by itself, but by Excel creating multiple invocations of that function, one for each element in the array, and then assembling the results in a result array.

But what if we had an explicit way to tell Excel to process each row or column in an array as a separate invocation of that scalar function just as it already knows to do when we pass an array where a scalar parameter is expected?

Consider a new function: SEGMENT. The purpose of the function is to instruct Excel to take a two-dimension array, segment it by row or by column, and pass each of those segments to the scalar function just as it knows how to do with our second array we are passing to the LARGE function for the "k" parameter to generate the five largest values as an array. The result would be a single dimension array of the scalar values produced for each of the segments generated by the SEGMENT function. Whether it is a row or column depends on how we instruct SEGMENT to operate: if by row than a column is returned; if by column than a row is returned.

The SEGMENT function syntax:

SEGMENT(array, [by_column], [indices], [size])
    array – any one or two-dimension array.
    by_column – Optional. Pass TRUE to segment the array by columns. Default is FALSE which results in the array segmented by rows.
    Indices – Optional. Pass an array of row or column indices to return. If omitted, then all rows or columns are returned. For example, if array is {1,2,3,4;2,3,4,5;3,4,5,6} and indices is {2,4} then two arrays are returned: {2,3,4} and {4,5,6}.
    Size – Optional. Pass a single integer representing the size of the returned arrays. If array is only one row or column wide, then passing this value tells the function to pass multiple overlapping arrays. For example, if array is {1,2,3,4,5} and size is 3 then three arrays are returned: {1,2,3}, {2,3,4}, {3,4,5}.

In our LARGE example, we can generate one array of all top five scores for all 20 students:

=LARGE(SEGMENT(A1:J20),{1,2,3,4,5})

The result would be 20 rows by 5 columns where each row is a single student, and each column is one of the five ranked values. The next step is to average each student's top five scores. We apply the SEGMENT function again and pass that result to the AVERAGE function:

=AVERAGE(SEGMENT(LARGE(SEGMENT(A1:J20),{1,2,3,4,5})))

The result is a single dimension array (a column) of 20 averages, one element per student. Ultimately, we want the maximum average across all students, so we pass this array to MAX as-is:

=MAX(AVERAGE(SEGMENT(LARGE(SEGMENT(A1:J10),{1,2,3,4,5}))))

And we have our scalar answer with one formula.

I suppose we could add an additional parameter to SEGMENT to tell it how many rows or columns constitute a single element in its result but that may be overkill.

As a final note, there are several posts in this forum for more array aware functions such as here:

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/38952709-sumcols-and-sumrows-functions-for-dynamic-arrays

I am pretty sure the SEGMENT function would be a solution for the many new functions that are being proposed without adding any new functions beyond the one proposed here.

Some more examples

Sum all columns in a table, one result per column:

=SUM(SEGMENT(Ref#,TRUE))

Average all rows in a table, one result per row:

=AVERAGE(SEGMENT(Ref#))

Add a running average of the last five values in a column, one result per row except for the first four rows:

=AVERAGE(SEGMENT(A1:A100,TRUE,,5))

Join the text values in rows, one result per row:

=TEXTJOIN(", ",TRUE,SEGMENT(A1:D20))

The use helper columns in tables now often is unnecessary if using the the new dynamic arrays. But if I want to use the output of one of the new functions as the source for a data validation list, I need to put the function in a cell and reference to that cell as the source for the data validation list. I would like to be able to put the dynamic array function directly into the source field of the data validation dialog.
Example: I have a table with a column of countries. I want to add a cell with a dropdown to chose one of those countries. I have to create a list of country names on the sheet using UNIQUE and SORT and then reference to the cell with this formula as the source for my data validation dropdown. I would prefer inputting the formula directly into the data validation without the helper list on the sheet.
(A similar suggestion has been posted for Excel on Mac, this adds and elaborates the suggestion for Excel for Windows.)