ExternelData_1 Error?

[Charley]

A Mac user just told me that a web query I sent him is giving him this error:

The following data range failed to refresh: ExternelData_1 Continue to refresh all?

Can anyone suggest a debug path? Or a reason for the error?

Power Query for the Mac is new, I believe. Could that be an issue? Somewhat similarly, most of the search results I've found about ExternelData_1 errors have come from non-English speakers.

Thanks!

Charley

 

[Ken Puls]

Hey Charley,

From MS's support site:

If you're an Office 365 subscriber, and have signed up for the Insiders Program, then you can refresh existing Power Query queries on your Mac for connections to Text, CSV, XLSX, XML and JSON files. The refresh functionality is live for all Office Insiders Fast customers in Excel for Mac, version 16.26 (19052200) and later.

So first question is if they're on the Insider build. Second question is if your data source file is one of those listed.

 

[Charley]

Ken,

It would have been nice if the page you quoted told us what the error message would be when those conditions aren't true. What's the link for that page?

The source file is a txt page from the web, using:

ID="MyPage",
Source=Table.FromColumns({Lines.FromBinary(Web.Contents("https://whatever/" & ID & ".txt"), null, null, 65001)}),

So that shouldn't be an issue, should it?

The Insider's program probably is the problem.

Thanks!

Charley

 

[Ken Puls]

To be honest, Charley, it is very likely to be Web.Contents() that is causing the issue. The page doesn’t mention that data source. Text files are pulled in using Csv.Document().



Sent from my iPhone using Tapatalk

 

[Charley]

Csv.Document() has two problems.

First, Csv.Document(MyPath) returns MyPath as the document. This means I've got to use Web.Contents() somewhat like an INDIRECT function, so it returns the document specified by the path.

Second, Csv.Document() returns a one-column CSV file, not a one-column TXT file. This is sort of like the problem you described here:
https://www.excelguru.ca/blog/2014/12/03/force-power-query-to-import-as-a-text-file/

So, do you know of a way for Mac PQ to load a text file from the Web?

 

[Ken Puls]

Hey Charley,

Download the txt document to a local file, then try to import it with power query. That should give you the correct syntax to do so and not get a single column. That csv connector is what they use for text files, so it should just need the internal parameters for the function set correctly.

If web.contents won’t work (which I suspect) you don’t have a lot of option but to try and hack this through. Even if you only get a single column csv, can you split the data manually?




Sent from my iPhone using Tapatalk