Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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
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
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
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.
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?
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.
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.
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.
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
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
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.
Did you click on the 'Table'?
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?