Forum Discussion

Markzolotoy's avatar
Markzolotoy
Impactful Individual
10 years ago

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 ...
Need Help
karekaasamoen's avatar
karekaasamoen
Frequent Visitor
10 years ago

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

 

 

Markzolotoy's avatar
Markzolotoy
Impactful Individual
10 years ago

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.

  • Markzolotoy's avatar
    Markzolotoy
    Impactful Individual
    10 years ago

    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?

    • karekaasamoen's avatar
      karekaasamoen
      Frequent Visitor
      10 years ago

      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