none
Excel 365 powerquery error 400 Bad request RRS feed

  • Question


  • I have Office 365 Proplus version 1906 11727.20230 CTR43-bit, English (Ireland) locale.
    This web site gives an error in Get Data From Web "Web.Contents failed to get contents" (400): Bad request

    https://www.kalóriaguru.hu/kaloriatablazat/barany-kaloriatablazat.php

    I can copy and paste from the web page table, I can use the old Get Data from Web Legacy Wizard providing I add the Magyar language pack to Windows (is there no normal international character recognition?)
    but the Get Data from Web in PQ is giving this error.

    It's not very informative. What is really wrong? Is there a way of getting more meaningful error messages?

    It may be a localisation issue.The URL kalóriaguru.hu is using Unicode characters and for a user in the US PQ translated it to a different web site xn--kalriaguru-ibb.hu
    That's what I see in a Punycode converter. I wonder why the US user's PQ converts it but mine does not?

    Setting the Query Options to English or Hungarian regional settings does not change the error in my PQ.
    What should I do to be able to access that site using its Unicode address, as the US user can do?
    Wednesday, July 10, 2019 8:47 AM

Answers

  • Hi there. Unicode characters are actually forbidden in URLs. Browsers hide this fact, but if you poke behind the curtain a bit you'll find they are automatically translating to Punycode for you. The URL you see if you look behind the scenes (https://www.xn--kalriaguru-ibb.hu/kaloriatablazat/barany-kaloriatablazat.php) is the actual URL of the website. The "xn--" part is a domain-name prefix, and the "-ibb" part is the encoding of the accented "o" character. PQ's initial request to determine what kind of content the URL is pointing at does not handle automatic Punycode translation, so you'll need to use the "real" URL in PQ.

    Ehren

    • Marked as answer by sysmod Thursday, July 18, 2019 9:02 AM
    Thursday, July 11, 2019 10:37 PM
    Owner

All replies

  • Hi there. Unicode characters are actually forbidden in URLs. Browsers hide this fact, but if you poke behind the curtain a bit you'll find they are automatically translating to Punycode for you. The URL you see if you look behind the scenes (https://www.xn--kalriaguru-ibb.hu/kaloriatablazat/barany-kaloriatablazat.php) is the actual URL of the website. The "xn--" part is a domain-name prefix, and the "-ibb" part is the encoding of the accented "o" character. PQ's initial request to determine what kind of content the URL is pointing at does not handle automatic Punycode translation, so you'll need to use the "real" URL in PQ.

    Ehren

    • Marked as answer by sysmod Thursday, July 18, 2019 9:02 AM
    Thursday, July 11, 2019 10:37 PM
    Owner
  • If PQ does not handle autmatic Punycode translation, then how did it work for this US user who reported to me:

    >>

    I didn't have any trouble using PQ to get data from the URL you provided. This is the Source step that was generated: =Web.Page(Web.Contents("https://www.xn--kalriaguru-ibb.hu/kaloriatablazat/barany-kaloriatablazat.php")) I'm in the US using Office 365 ProPlus Build 11727.20230 Click-to-Run.

    <<

    Hence my question "I wonder why the US user's PQ converts it but mine does not?"

    Does PQ use a browser to get the web page and if so which one - some embedded Microsoft one, or the default browser for the user?

    Saturday, July 13, 2019 8:58 PM
  • I'm not sure, as I'm a US user and I got the same error you did. Is it possible the user in question opened the link you provided in a browser and copied the URL from there? When I do that, I get the translated version of the URL.

    Ehren

    Monday, July 15, 2019 4:19 PM
    Owner
  • Yes, Ehren, I asked him and he said

    The difference in my case, Patrick, as the gentleman who responded

    to your post correctly guessed, is that I probably copied the

    link from Chrome and pasted it into the Get Data from Web dialog

    box. It pastes as: https://www.xn--kalriaguru-ibb.hu/kaloriatablazat/barany-kaloriatablazat.php If I copy the link from Technet and paste it into the dialog I do get an error Details: "The remote name could not be resolved: 'www.kalóriaguru.hu'"


    SO, when I open the link in Chrome or Firefox, it does NOT appear to change in the address bar, but it DOES copy out as Punycode.

    So, basically, anyone who wants to use PQ with international web sites has to copy & paste them through a browser first. Pretty unhelpful of MS, I would have thought.



    • Edited by sysmod Wednesday, July 17, 2019 2:06 PM
    Wednesday, July 17, 2019 11:54 AM
  • Good to know. The lack of automatic translation to Punycode wasn't an explicit decision on our part (in fact, this is the first time I've heard of it). It's a limitation in the .NET libraries we use, which don't support translating to Punycode without app-level configuration changes. Now that it's on our radar, we could consider making these config changes, but doing so could potentially break backward compatibility with other existing behaviors our customers rely on. And in order to ensure consistency among various hosts (Excel, Power BI, etc.) we would usually prefer to make such changes via code, not configuration.

    Ehren

    Wednesday, July 17, 2019 5:30 PM
    Owner
  • Well, in terms of existing behaviour that people relied upon, the legacy wizard worked just fine for the Hungarian user who first reported that to me when their Excel was updated to new PQ. I showed them how to get the legacy wizard back.

    As I said in my original post, I can use the old Get Data from Web Legacy Wizard providing I add the Magyar language pack to Windows .  So PQ is LESS capable in that respect than the old wizard.

    So, do please consider allowing the end-user to enter their web site address as they know it without having to translate it into American. Sure, code is no problem, and I'd use VBA, but this is for users who don't know code.

    Thanks, Ehren.

    Thursday, July 18, 2019 9:02 AM