topic Re: Get data from webservice in Power Query

Get data from webservice

Markzolotoy — Tue, 09 Aug 2016 13:49:08 GMT

I am trying to bring data into my dashboard by accessing a websevice endpoint. It works fine in my mobile report wher i am using a dataset based on an xml data source. After proving URL of that data source I get the following screen:

After Loading it I dont see any data.

Any idea?

Thanks

Re: Get data from webservice

suvaries — Thu, 11 Aug 2016 08:45:26 GMT

Did you click on the 'Table'?

Re: Get data from webservice

Markzolotoy — Fri, 12 Aug 2016 19:43:17 GMT

Ok, first I get this:

After Edit I get this:

Then I click on Table and get this:

Then I click on Table again and get this:

At this point nothing to click on.

Any more ideas?

Re: Get data from webservice

v-qiuyu-msft — Sat, 13 Aug 2016 05:53:38 GMT

Hi Markzolotoy,

In Power BI, the Web data source type is used for importing a table of data from a Web page. You can take a look at this article: Tutorial: Importing and analyzing data from a Web Page using Power BI Desktop.

In your scenario, as the web page doesn't contain any table data, you only retrieve element data in Query Editor. I would suggest you use XML data source instead of Web URL to retrieve data.

Best Regards,
Qiuyun Yu

Re: Get data from webservice

Markzolotoy — Sat, 13 Aug 2016 10:21:29 GMT

When I choose XML as a data source it prompts me to seldect a file, but I want to get it from the webservice.

Re: Get data from webservice

v-qiuyu-msft — Mon, 15 Aug 2016 11:58:38 GMT

Hi Markzolotoy,

You can see when you use web data source to get data, you just need to place the URL and extract records from the dataset. But in your scenario, the extracted records don't contain any fact data. So it's not a good option to use web data source if you want to get fact data.

Best Regards,
Qiuyun Yu

Re: Get data from webservice

Markzolotoy — Mon, 15 Aug 2016 13:56:46 GMT

How come if I save my data into a file and then use that file for a data source then everything works fine? Search for my other message about json data. Same problem. Cannot get data when it's returned by a webservice, but the same json from the file is working fine.

Re: Get data from webservice

Youssef — Mon, 15 Aug 2016 17:00:59 GMT

You should be able to connect to Restful Web Services that return valid Json using the GetData from Web in Power BI Desktop.

From the screenshot, it seems that what returns from the URL you put is not Json but HTML. You can monitor the traffic with Fiddler and make sure what's coming back is actual json.

Hope this helps.

Re: Get data from webservice

Markzolotoy — Mon, 15 Aug 2016 17:30:20 GMT

Do I need to change my data source after it's been created to say Json? By default it says HTML.

But the reason I am not gettng json id because my webservice is not returning pure json?

Re: Get data from webservice

Youssef — Mon, 15 Aug 2016 17:30:49 GMT

What is the data type you are expecting from your webservice end point? Is it Json? If so, they Power BI should perfectly support this scenario. Let me know if that's the case and you are still facing issues.

If the data you are expecting is HTML data like a web page, you can refer to the articly linked in one of the replies above on more details on that. You might have to do some parsing and cleaning up in this case.

Re: Get data from webservice

Markzolotoy — Mon, 15 Aug 2016 17:33:58 GMT

I am definetely expecting json. This is what I see in Chrome:

{"root":[{"catchCount":127,"DisplayName":"Forward to Diagnostics Team","SeriesName":"Alarm Category"},{"catchCount":2685,"DisplayName":"Retrain Model","SeriesName":"Alarm Category"},{"catchCount":15,"DisplayName":"True Alarm","SeriesName":"Alarm Category"},{"catchCount":213,"DisplayName":"Contact Plant","SeriesName":"Alarm Classification"},{"catchCount":9,"DisplayName":"Contact Support","SeriesName":"Alarm Classification"},{"catchCount":2600,"DisplayName":"Monitor","SeriesName":"Alarm Classification"},{"catchCount":4,"DisplayName":"Schedule Maintenance","SeriesName":"Alarm Classification"},{"catchCount":204,"DisplayName":"Electrical Problem","SeriesName":"Alarm Clear"},{"catchCount":9,"DisplayName":"Historian Offline","SeriesName":"Alarm Clear"},{"catchCount":2609,"DisplayName":"Mechanical Problem","SeriesName":"Alarm Clear"},{"catchCount":2,"DisplayName":"Network Problem","SeriesName":"Alarm Clear"},{"catchCount":9,"DisplayName":"Stuck Valve","SeriesName":"Alarm Clear"}]}

Re: Get data from webservice

Youssef — Mon, 15 Aug 2016 18:33:27 GMT

Most likely this is received as HTML (although you are only seeing the JSON in the browser). If you right click in chrome, and click view source, you will see that this JSON snippet is likely encapsulated in HTML. If you can make sure your service sends pure json Power BI should resolve it as JSON with no problems.

Re: Get data from webservice

Markzolotoy — Mon, 15 Aug 2016 18:42:42 GMT

Nope, page source shows exactly the same. So does Charles.

Re: Get data from webservice

Youssef — Mon, 15 Aug 2016 20:50:55 GMT

I sent you a private message to investigate further. You can update the thread once a resolution is reached.

Re: Get data from webservice

karekaasamoen — Tue, 16 Aug 2016 12:23:57 GMT

I'm relatively new to PowerBI and powerQuery, and maybe you have had this problem sorted out allready, but it seems like you have drawn data from an url without formatting it as xml data. I guess you have use a formula like:

Source = Web.Contents(https://some.url.here)

Which by default will return a HTML file. Try to wrap the web-connector in an XML formula like this:

Source = Xml.Tables(Web.Contents(https://some.url.here))

And I guess you will be fine

Re: Get data from webservice

karekaasamoen — Tue, 16 Aug 2016 12:24:27 GMT

Source = Web.Contents(https://some.url.here)

Which by default will return a HTML file. Try to wrap the web-connector in an XML formula like this:

Source = Xml.Tables(Web.Contents(https://some.url.here))

And I guess you will be fine

Re: Get data from webservice

Markzolotoy — Tue, 16 Aug 2016 14:42:36 GMT

Where exactly do I place that formula?

Re: Get data from webservice

karekaasamoen — Wed, 17 Aug 2016 05:22:26 GMT

The best way is to use the advanced query editor. From the analysis-model (main window), click the Edit Queries button

Then, right-click the actual datasource and select advanced editor

I guess the source of your data looks quite similar to this Source = ( Web.Page(Web.Contents(....)

Change this to Xml.Tables(Web.Contents and I guess you will have your data as expected. The advanced editor uses PowerQuery scripting language, is flexible and gives a lot of opportunities, but if you haven't used this you have a certain learning curve. But try it out

Re: Get data from webservice

Markzolotoy — Thu, 18 Aug 2016 15:04:30 GMT

No worki.

The code looks like this:

let
Source = Xml.Tables(Web.Contents("woking path to webservice. works everywhere except powe bi")),
Data0 = Source{0}[Data]
in
Data0

error:

Expression.Error: The field 'Data' of the record wasn't found.
Details:

Name="name of working everywhere else webservice"
Table=Table

What's totally f-p is that the same xml copied into a file works fine.

Re: Get data from webservice

Markzolotoy — Thu, 18 Aug 2016 15:35:37 GMT

Ok, here is what works, kinda.

After realizing it works when an xml is pasted intoa file I used file query and made it work for the webservice.

Here the query code:

let
Source = Xml.Tables(Web.Contents("woking path to webservice. works everywhere except powe bi")),
Table0 = Source{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table0,{{"Attribute:catchCount", Int64.Type}, {"Attribute:DisplayName", type text}, {"Attribute:SeriesName", type text}})
in
#"Changed Type"

Can someone please explain how it is possible to get from defualt query to the one that worked without using a file first?