Forum Discussion

jitpbi's avatar
jitpbi
Post Patron
5 years ago
Solved

Dynamic values in paramater

Hi,   I am using the below paramters to pass in the URL to get the data from a web source which based on the start date and end date:     the below is from Advance Editor:   let Sou...
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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'.

'http://175.20.9.16:41000/PS/v1/ABC/Device/Meter?start_time=2020-00-30T09:00:00Z&end_time=2020-08-02T...'
Regards,

Xiaoxin Sheng

jitpbi's avatar
jitpbi
Post Patron
5 years ago

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

 

Discover Data Sources

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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
	Result

    Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code 
    Regards,

    Xiaoxin Sheng

    • jitpbi's avatar
      jitpbi
      Post Patron
      5 years ago

      Hi,

       

      I am getting an error on Power BI service to schedule a refresh, however It is successfully refreshing from PBI desktop.:

       

      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

      Discover Data Sources

      Query contains unsupported function. Function name: Web.Contents

       

      The below is my code:

       

      let

      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")))

      in 

         Source

      • lbendlin's avatar
        lbendlin
        Super User
        5 years ago

        Parameterize your query. You want to have a static base URL as a source.

    • jitpbi's avatar
      jitpbi
      Post Patron
      5 years ago

      Hi Anonymous ,

       

      Thank you for the soultion. All resolved now.