cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Markzolotoy
Impactful Individual
Impactful Individual

Get data from webservice

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:

Capture.PNG

After Loading it I dont see any data. 

 

Any idea?

 

Thanks

20 REPLIES 20
karekaasamoen
Frequent Visitor

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

Where exactly do I place that formula?

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

 

EditQueryButton.png

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

 

RightClickDatasource.png

 

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

 

ChangeSource.png

 

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

 

 

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.

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?

Try changing the line

 

Data0 = Source{0}[Data]

 

to

 

Data0 = Source{0}[Table]

 

and I guess it will work out fine. The powerquery script interprets [Data] as a field and not a table

karekaasamoen
Frequent Visitor

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

Youssef
Microsoft
Microsoft

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.

Youssef Shoukry
Product Manager, Power BI
Markzolotoy
Impactful Individual
Impactful Individual

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

Capture.PNG

 

 

 

 

 

 

 

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

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.

Youssef Shoukry
Product Manager, Power BI
Markzolotoy
Impactful Individual
Impactful Individual

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"}]}

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.

 

Youssef Shoukry
Product Manager, Power BI
Markzolotoy
Impactful Individual
Impactful Individual

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

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

Youssef Shoukry
Product Manager, Power BI
v-qiuyu-msft
Community Support
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

suvaries
Frequent Visitor

Did you click on the 'Table'?

Markzolotoy
Impactful Individual
Impactful Individual

Ok, first I get this:

Capture.PNG

After Edit I get this:

Capture.PNG

 

 

 

 

 

Then I click on Table and get this:

Capture.PNG

 

 

 

 

 

 

Then I click on Table again and get this:

Capture.PNG

 

 

 

 

 

At this point nothing to click on. 

 

Any more ideas?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors