Results 1 to 6 of 6

Thread: ExternelData_1 Error?

  1. #1
    Acolyte Charley's Avatar
    Join Date
    Jul 2017
    Posts
    61
    Articles
    0
    Excel Version
    365

    ExternelData_1 Error?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Acolyte Charley's Avatar
    Join Date
    Jul 2017
    Posts
    61
    Articles
    0
    Excel Version
    365
    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

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Acolyte Charley's Avatar
    Join Date
    Jul 2017
    Posts
    61
    Articles
    0
    Excel Version
    365
    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/1...s-a-text-file/

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

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •