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