Microsoft Query can't find tables in the Excel file

Question

I'm trying to get a 4-column cross-merge in Excel to properly show all of the possible combinations for a thing. (Location, Step, Type). My research indicated that using a MS cross-join query was the way to go about this. http://www.contextures.com/excelmsquerycartesian.html

Ok, great, let's go through the steps.

Problem is, when I try to select tables from the file, I get the error message as detailed in this article: https://support.microsoft.com/en-us/help/286891/you-may-not-access-excel-worksheets-by-using-ms-query

So I went and followed the directions. Problem was, my version of word (365 - for both Excel and Word) diverged at one point from the instructions, and no amount of clicking around managed to get me through the steps, and got Excel tables working properly.

Ideally I'd like to get the three parts of the merge in separate columns. How can I best accomplish this combinatorics table to get every possible outcome, that auto-updates? Hoping to auto grow and shrink as new data is added to the start, which is a significant portion of why I'm hellbent on using a query over just formulas, but I might be willing to call uncle and use some formulas.

So onto the question: How can I get this working? Either a different method, a solve for the missing table issue, or even how to properly "kick" excel to recognize the table would all work.

Answer 1

So it turns out that the article here: https://www.excelguru.ca/blog/2016/05/11/cartesian-product-joins-for-the-excel-person/ neatly describes how to do what I want in just power query.

Since link-only answers are discouraged:

1) Add all the queries we want to the data model 2) Start a new query on the column we want 3) Add new column 4) Add custom column 5) In the formula, just type in the name of the query 6) Expand the table

And that's it! Much easier and simpler than I could have hoped for.