Forum Discussion

jitpbi's avatar
jitpbi
Post Patron
6 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,

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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago
        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.