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

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

Markzolotoy's avatar
Markzolotoy
Impactful Individual
10 years ago

Where exactly do I place that formula?

  • 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?