Search code examples
excelexcel-2013excel-web-query

Web query connection does not create table in excel

I have the below external link

https://creator.zohopublic.com/inovadms/inova-dms/view-embed/SPO_Export_Public/M1ZxYdK6k6TYpgZzX7BgHsSQEwSQ2aY27Fx7DHTfCtNgp3tvsUHTajsMxdekudO9hyR9MFe0SgbF8MBngY6UbW5YBmgjWuvk5WfQ/hotelrecid=1721972000009874027&type=1

I would like to connect it in Excel 2013 using web query (with the Power Query it works but I would like to implement it as web query from the Data tab). In a new excel sheet I am doing the below

1) Open a blank sheet

2) Go to Data-> From Web

3) I add the link and click GO. It returns me the below. I am not sure if this error is the reason of my problem

enter image description here

4) By clicking 'Yes' I have the below (everything fine until now)

enter image description here

But when I click the import button (and select the first cell for this import) it returns an 'Open Page Builder' message. Why this happens? I did not have this problem until last year and I do not understand what is the issue here.

Zoho Support told me that it is Microsoft issue. Can you please reproduce it and tell me why it does not show me the page in an excel table?

enter image description here

It should return be something like that (without the color format)

enter image description here

Solution

  • It works for me by following this method.

    1) Open a blank sheet

    2) Go to Data-> From Web

    3) I add the link and click GO. It returns me the below

    zoho_a

    4). I choose Basic option and enter URL and click OK

    5). I get the following screen

    zoho_b

    6). I Choose the second option that is Table. As per my experience data desired by us is contained in Tables.

    zoho_c

    7). I click Load button to get the data, which loads on web page correctly.

    zoho_d

    HTH A). If you choose Advanced option in step 3 then we will get the following dialog box. In this box in upper part we can add a URL by its constituent parts. For example http://community.powerbi.com/t5/Desktop/How-to-setting-a-dynamic-URL-connection/td-p/57072

    has been shown entered into its constituent parts in the snapshot shown below. Further details to set a dynamic URL connection can be referred in linked document.

    zoho_f1 B). In the lower part of the dialog box there is a drop down to select HTTP Request Header Parameters like 

    Accept              Required. Set this to application/json.
Accept-Encoding     Optional. Supported encodings are gzip and deflate.
Authorization       Required. See authentication.
Connection          Optional. It is recommended that Keep-Alive be enabled.
Content-Length      Optional. It is recommended taht the request body length be specified when known.

    etc.... Further details can referred Query Management HTTP Request HTTP Request Header Parameters setting for various applications like 

    Enter data directly into Power BI Desktop
Connect to Excel in Power BI Desktop
Analysis Services multidimensional data in Power BI Desktop
Connect to CSV files in Power BI Desktop
Connect to a Google BigQuery database in Power BI Desktop
Connect to an Impala database in Power BI Desktop
Connect to an OData feed in Power BI Desktop

    etc.. can be referred to power-bi/desktop-connect-excel

    Edit: Regarding OP's comment whether same Excel file can run on another PC, my opinion is it is not possible. When try to copy file to temporary storage for transfer, we get a warning that this file can not be copied to new location with its properties. Are you sure you want to copy without its properties. Please see this warning snap shot.

    query connection warning

    As such it seems that the same query may not work in a new location. New query has to be created for that location.