Jeff Weir published a post at DDOE last week on global names freaking out when a local name is encountered.  It reminded me that I ran into something similar when I was testing text functions in Power Query a while back; a naming conflict when I created a table from Power Query.

Interestingly, I can replicate this without Power Query at all using just native table functionality.

Set up a Table

To begin with I created a very simple table:

Then I gave the table a name.  In this case, for whatever reason, I chose “mid”:

Enter Wonkiness (Naming Conflict)…

Okay, it’s a weird table name.  I get that.  But in my original example I was comparing Power Query’s Text.Range function with the MID function, which is why I named my table mid…  anyway…

Add a new column and type in =MID

You can see that we’ve plainly chosen the MID that refers to the function, not the table.  I even set the capitalization correctly to make sure I got the right one.  Now complete the formula:

=MID([@Product],3,1]

And press Enter:

Nice!  Apparently Excel is too smart for it’s own good and overrules the interpretation of built in functions with table names, resulting in a #REF! error.

Fixing the issue

The solution to fix this should be pretty obvious… rename the table.  When you do, you’ll see that it also updates the formula:

Plainly Excel was very confused! So now we just need to fix the formula:

And we’re good.  🙂

End Thoughts

To cause this issue from Power Query, you simply need to give your query a name that conflicts with an Excel function (like MID).  When it's loaded to an Excel table, that table inherits the query name as the table name.

The naming conflict issue has probably existed since tables were implemented in Excel.  It’s not good, but at the same time, it’s taken me a long time to trip on this, as I don’t usually use a table name that conflicts with a built in function name… at least not one that I use.

Long story short:  Avoid naming your tables (or Power Queries) after Excel function names.  😉