Forum Discussion
Dynamic values in paramater
Hi jitpbi,
Perhaps you can try to split your url string and add optional parameters 'relative path' and 'query' to confirm if they works on power bi service side:
let url="http://175.20.9.16:41000/", Source = Web.Contents(url, [ RelativePath="PS/v1/ABC/Device/Meter", Query = [ start_time = DateTimeZone.ToText(DateTimeZone.From(Date.AddDays (DateTimeZone.UtcNow(),-3)),"yyyy-MM-ddThh:mm:ssZ"), end_time = DateTimeZone.ToText(DateTimeZone.UtcNow(),"yyyy-MM-ddThh:mm:ssZ") ] ]), Result=Json.Document(Source) in ResultUsing The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code
Regards,Xiaoxin Sheng
I got the solution for this in my another post:
https://community.powerbi.com/t5/Desktop/Dynamic-values-in-paramater/m-p/1346168#M578929
Hi jitpbi,
It seems like you want to get the current date with UTC format and using in the connector.
If this is a case, you can consider using DateTimeZone functions to get the current date and format as specific UTC format you mention then concatenate with your raw URL strings.
let
Source = Json.Document(Web.Contents("http://175.20.9.16:41000/PS/v1/ABC/Device/Meter?start_time=" & DateTimeZone.ToText(DateTimeZone.From(DeviceSD),"yyyy-mm-ddThh:mm:ssZ") & "&end_time=" & DateTimeZone.ToText(DateTimeZone.UtcNow(),"yyyy-mm-ddThh:mm:ssZ")))
in
SourceRegards,
Xiaoxin Sheng
Hi Anonymous ,
I am getting the below error when tried this:
DataSource.Error: Web.Contents failed to get contents from 'http://175.20.9.16:41000/PS/v1/ABC/Device/Meter?start_time=2020-00-30T09:00:00Z&end_time=2020-08-02T08:08:22Z' (500): time data '2020-00-30 09:00:00' does not match format '%Y-%m-%d %H:%M:%S'
Thanks
Hi jitpbi,
My formula works will and these DateTime values are correctly formatted as the date timezone format as your methods.
According to your error message, I think this issue should more relate to your parameters. Please double-check and modify your query parameter 'start date', it seems like you are try to input an invalid start date with the month '0'.
Xiaoxin Sheng
Hi Anonymous ,
It worked for me after doing a small change in the format
from "yyyy-mm-ddThh:mm:ssZ" to "yyyy-MM-ddThh:mm:ssZ"
But getting the below challenege in scheduling the refresh after publishing to Service:
You can't schedule refresh for this dataset because the following data sources currently don't support refresh:
- Data source for Table1
- Data source for Table2
Query contains unsupported function. Function name: Web.Contents
However, it is successfully refreshing from PBI desktop.
Table1 and Table2 are ones where the suggested format concatenated in the URL strings:
Source = Json.Document(Web.Contents("http://175.20.9.16:41000/PS/v1/ABC/Device/Meter?start_time=" & DateTimeZone.ToText(DateTimeZone.From(Date.AddDays (DateTimeZone.UtcNow(),-3)),"yyyy-MM-ddThh:mm:ssZ")
& "&end_time=" & DateTimeZone.ToText(DateTimeZone.UtcNow(),"yyyy-MM-ddThh:mm:ssZ")))Thanks