2

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.

Screenshot - Queries & Connections button

Any input is appreciated.

Vertical Sunlight
  • 31
  • 4
  • 1
    What are the versions of you and your colleague's Excel? And is the file path **relative** or **absolute**? – user96931 Feb 19 '20 at 16:00
  • The file path is absolute. I am on version 16.0.10730.20432. Theirs was slightly different, I don't have it handy but it I think it was 16.0.xxxxx (after 16.0 it was different than mine) – Vertical Sunlight Feb 19 '20 at 16:53
  • Sounds like the Excel version . – Mike Honey Feb 20 '20 at 15:39
  • 1
    Found out my coworker had Excel 2016 while I have Excel 365 MSO. This is probably the reason. I will be trying to determine exactly what difference between the two is causing this. – Vertical Sunlight Feb 24 '20 at 14:04

1 Answers1

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.

Vertical Sunlight
  • 31
  • 4