Forum Discussion

PetrusJKruger's avatar
PetrusJKruger
Frequent Visitor
8 years ago

Workaround powerbi.com Requirement for Static URL in Web.Contents() Function


Good day,

Background

I'm trying to access our database through a Web Datasource. To query the database, I must the URL in the form of

 

http://<server>:[<port>]/query/<database>/<language>/<query-text>[/<limit>].

To get the <query text>, I at first used the Uri.EscapeDataString() function to encode my "SELECT name FROM table" query. This of course worked great in PBI Desktop, but failed in pbi.com. This is a known issue discussed here, with a workaround. It uses the Query header, but this inserts a '?' between the URL and the <query-text>, which isn't acceptable. Similarly, I attempted the RelativePath header, but that inserts a '/' character.

 

No doubt, the above would have worked if <query-text> was an optional item like the [/<limit>] part.

Temporary Solution
My solution was to write the output of Uri.EscapeDataString(<query-text>) to a parameter, copy that text just before publishing and paste that static text into the Web.Contents() function to acquire the necessary output. This way, I at least have report that can be uploaded to pbi.com and get refreshed according to a schedule.

New Problem

My next goal is to merge the results from two table from two different sources, a history table stored in a data warehouse and a 'current' table queried from the transactional database. My goal is to query the last date available from the history table and merge that with a query of the transactional database like:

SELECT data FROM table WHERE date_created > max_historic_date

Obviously, applying my temporary solution above would require me to manually update the M code in the .pbix file every time the history table gets updated.

Can someone see any way to work around this, remembering that I cannot use a function to cast "max_historic_date" to before concatenating it to the encoded <query-text>?

Your input would be greatly appreciated.

Kind regards,
Petrus Kruger

 

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi PetrusJKruger,

     

    What's the web data source? Can you create a view in the database? Then you can use the simple query like this: "SELECT name FROM VIEW".

     

    Best Regards,

    Dale

    • PetrusJKruger's avatar
      PetrusJKruger
      Frequent Visitor
      8 years ago

      Hi v-jiascu-msft,

      Thanks for coming back to me.

      The websource is a OrientDB managed database, which allows access through a RESTful HTTP protocol.

      I'm unfortunately not familiar with what you mean by 'view' of the database - I'm very much learning as I go along. Perhaps I'm not answering your question, but I'll attempt to.

      I suspect that 

      http://<server>:[<port>]/query/<database>/<language>/<query-text>[/<limit>]

      is how you access the 'view'. Because it is an HTTP request the URL needs to be encoded from

       

      "SELECT name FROM VIEW" to "SELECT%20name%20FROM%20VIEW"

      I can work around that manually without the Uri.EscapeDataString() function. However, now I need to add a WHERE clause that changes dynamically and still use no functions like Date.ToText(max_historic_date). Then I need to append that to become part of the <query-text>, hoping that using & to concatenate will still allow powerbi.com to perform its static web source test.

      I hope that helps, v-jiascu-msft.

      Kind regards,