Need help building query with dynamic data source to set with scheduled refresh

[Anonymous]

I've seen lots of articles for a workaround in setting a scheduled refresh with a dynamic data source but can't get the query right... 

The query that I have is below and unfortunately unable to refresh in the service because of ("https://communications.contoso.com/ssv3/odata/SentMessages(" & Text.From([ID]) & ")/ContactReads")

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"

1. https://sqlserverbi.blog/2018/10/21/web-api-data-sources-with-power-query-and-scheduling-data-refres...
2. https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...
3. https://blog.crossjoin.co.uk/2019/04/25/skip-test-connection-power-bi-refresh-failures/
4. https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...
5. https://www.mattmasson.com/2014/11/iterating-over-multiple-pages-of-web-data-using-power-query/

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],
IDs = #"60f58808-4a16-43ee-86e8-9b6bcd3f37d4"{[entity="FromBIs-InvididualTracking"]}[Data],
  EmailIDs = (IDs) => 
    let
      Source = Json.Document(
          Web.Contents(
              "https://communications.contoso.com/ssv3/odata/SentMessages(" & Text.From(IDs) & ")/ContactReads"
            )
        )[value],
      Success = Source[ID]
    in
      Success,
  Output = Table.AddColumn(IDs, "WebContents", each EmailIDs([ID]))
in
  Output

Here ^ is my attempt but getting the error below:

Expression.Error: We cannot apply field access to the type List.
Details:
Value=[List]
Key=ID

[Anonymous]

anyone? 😞 😫

[Anonymous · Accepted solution]

Web.Contents("https://communications.contoso.com ", [RelativePath=" ssv3/odata/SentMessages(" & Text.From([ID]) & ")/ContactReads"])

was able to get it with the above ^