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

  • jitpbi's avatar
    jitpbi
    Post Patron
    5 years ago

    Hi Anonymous ,

     

    Thank you for the soultion. All resolved now.