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
