Dynamic values in paramater

[jitpbi]

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
Source = Json.Document(Web.Contents("http://175.20.9.16:41000/PS/v1/ABC/Device/Meter?start_time=" & DeviceSD & "&end_time=" & DeviceED))

in

Source

I can successfully get the data for these paramaters (hard coded date), Now my requirement is to make these current values dynamic. The current value for the parameters "DeviceED" should be current datetime (like now()) and "DeviceSD" should be 3 days back datetime (like now()-3) rather than passing a hard coded datetime values.

Please suggest how to achieve this.

StartDate

EndDate

[Anonymous · Accepted solution]

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

[Anonymous]

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.

DateTimeZone functions 

DateTimeZone.ToText 

DateTimeZone.UtcNow 

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
Source

Regards,

Xiaoxin Sheng

[jitpbi]

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

[Anonymous]

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]

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 · Accepted solution]

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]

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

[Anonymous]

asking for assistance with this as well, see here https://community.powerbi.com/t5/Service/Need-help-building-query-with-dynamic-data-source-to-set-wi...

[lbendlin]

Same approach - find a URL portion that produces some sort of result, for example https://communications.contoso.com and then parameterize the rest.

[Anonymous]

---

@lbendlin wrote:

Same approach - find a URL portion that produces some sort of result, for example https://communications.contoso.com and then parameterize the rest.

---

let
Source = PowerBI.Dataflows(null),
#"2242300f-5b99-4370-881d-c3616d5ca075" = Source{[workspaceId="2242300f-5b99-4370-881d-c3616d5ca075"]}[Data],
#"60f58808-4a16-43ee-86e8-9b6bcd3f37d4" = #"2242300f-5b99-4370-881d-c3616d5ca075"{[dataflowId="60f58808-4a16-43ee-86e8-9b6bcd3f37d4"]}[Data],
#"FromBIs-InvididualTracking1" = #"60f58808-4a16-43ee-86e8-9b6bcd3f37d4"{[entity="FromBIs-InvididualTracking"]}[Data],
#"Added Custom" = Table.AddColumn(#"FromBIs-InvididualTracking1", "WebContents", each Json.Document(Web.Contents("https://communications.contoso.com/ssv3/odata/SentMessages(" & Text.From([ID]) & ")/ContactReads"))[value]),
#"Expanded WebContents" = Table.ExpandListColumn(#"Added Custom", "WebContents"),
#"Filtered Rows" = Table.SelectRows(#"Expanded WebContents", each ([WebContents] <> null)),
#"Expanded value1" = Table.ExpandRecordColumn(#"Filtered Rows", "WebContents", {"TimeRead", "OpenCount", "DeviceCount", "DisplayName", "FirstName", "LastName", "Email", "ActiveState", "CreationDate", "Shared", "OwnerID", "CategoryID", "StageID", "Company", "WebAddress", "Title", "FileAs", "Source", "Notes", "BusinessID", "RegionID", "IsMobile", "CountOfIPAddress"}, {"TimeRead", "OpenCount", "DeviceCount", "DisplayName", "FirstName", "LastName", "Email", "ActiveState", "CreationDate", "Shared", "OwnerID", "CategoryID", "StageID", "Company", "WebAddress", "Title", "FileAs", "Source", "Notes", "BusinessID", "RegionID", "IsMobile", "CountOfIPAddress"})
in
#"Expanded value1"

My challenge here is trying to follow the syntax in chris webb's blog linked below, in the example, the queried value (q=”cows”) is at the end of the base url, where as on my source its in the middle, 

1. https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

Any help is greatly appreciated.

let

    Source = PowerBI.Dataflows(null),

    #"2242300f-5b99-4370-881d-c3616d5ca075" = Source{[workspaceId="2242300f-5b99-4370-881d-c3616d5ca075"]}[Data],

    #"60f58808-4a16-43ee-86e8-9b6bcd3f37d4" = #"2242300f-5b99-4370-881d-c3616d5ca075"{[dataflowId="60f58808-4a16-43ee-86e8-9b6bcd3f37d4"]}[Data],

    #"FromBIs-InvididualTracking1" = #"60f58808-4a16-43ee-86e8-9b6bcd3f37d4"{[entity="FromBIs-InvididualTracking"]}[Data],

    #"Added Custom" = Table.AddColumn(#"FromBIs-InvididualTracking1", "WebContents", each Json.Document(Web.Contents(Web.Contents(

        "https://communications.contoso.com",

        [

         RelativePath="ssv3/odata/SentMessages(",

         Query=[Text.From([ID]) & ")/ContactReads")]

        ])[value]),

    #"Expanded WebContents" = Table.ExpandListColumn(#"Added Custom", "WebContents"),

    #"Filtered Rows" = Table.SelectRows(#"Expanded WebContents", each ([WebContents] <> null)),

    #"Expanded value1" = Table.ExpandRecordColumn(#"Filtered Rows", "WebContents", {"TimeRead", "OpenCount", "DeviceCount", "DisplayName", "FirstName", "LastName", "Email", "ActiveState", "CreationDate", "Shared", "OwnerID", "CategoryID", "StageID", "Company", "WebAddress", "Title", "FileAs", "Source", "Notes", "BusinessID", "RegionID", "IsMobile", "CountOfIPAddress"}, {"TimeRead", "OpenCount", "DeviceCount", "DisplayName", "FirstName", "LastName", "Email", "ActiveState", "CreationDate", "Shared", "OwnerID", "CategoryID", "StageID", "Company", "WebAddress", "Title", "FileAs", "Source", "Notes", "BusinessID", "RegionID", "IsMobile", "CountOfIPAddress"})

in

    #"Expanded value1"

1.  

[jitpbi · Accepted solution]

I got the solution for this in my another post:

https://community.powerbi.com/t5/Desktop/Dynamic-values-in-paramater/m-p/1346168#M578929

[lbendlin]

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

[jitpbi]

Hi @Anonymous ,

Thank you for the soultion. All resolved now.

[amitchandak]

@jitpbi , not very clear. try like DateTime.LocalNow