Excel sheet with query to local file does not work on other machine

Question

I have created an Excel sheet that pulls data from local files with powerquery. The sheet does not work on my colleague's machine. We are not sure why.

It works like this:

1. Paste path to local file in a table named FilePath
2. Click "refresh all" in data tab

3. A connection GetFilePath gets the file path using this expression:

   = (rangeName) => 
   Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1]
   

4. A query loads that file into a sheet with this source:

   = Csv.Document(File.Contents(GetFilePath("FilePath")),[Delimiter="#(tab)", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None])
   

The error my coworker gets is apparently with step 3. When refresh is clicked this error occurs:

[Expression.Error] We couldn't find an Excel table name 'FilePath'.

We have verified that the table is named FilePath on their machine, same as on mine.

One difference I noticed between my machine and my coworker's:

In the ribbon, data tab, Queries & Connections section, I have a button that says "Queries & Connections". For my coworker the same button just says "Connections".

When I click the button I get the Queries & Connections sidebar. When my coworker clicks their Connections button, they get a popup that looks quite different from the sidebar.

Any input is appreciated.

Answer 1

This was apparently due to the fact that I have Excel for Office 365 and my coworker has Excel 2016.

It seems the two versions handle custom table names/cell references differently. Here is what I changed to fix it:

Original expression:

= (rangeName) => 
Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1]

Where "FilePath" is a custom table name and "Column1" is some default (not the actual column name in the sheet).

Fixed expression:

= (rangeName) => 
Excel.CurrentWorkbook(){[Name="Table3"]}[Content]{0}[File Path]

Where "Table3" is the default name Excel gave the table and "File Path" is the column name actually displayed in the cell.

After making these changes the sheet now works in both Excel 2016 and Excel 365.

I don't quite understand why the custom table name was breaking Excel 2016. It didn't seem to acknowledge the cells referred to by that name as a table. If anyone has any info on exactly what went wrong I'd love to hear it.