A dynamic data source is a data source in which some or all of the information required to connect cannot be determined until Power Query runs its query, because the data is generated in code or returned from another data source. Examples include: the instance name and database of a SQL Server database; the path of a CSV file; or the URL of a web service.
Power BI datasets that use dynamic data sources cannot be refreshed in the Power BI service. Thee only way to work around this is to use use the RelativePath and Query options with the Web.Contents M function. Queries that reference Power Query parameters can also be refreshed.
Although, the trick of using the RelativePath and Query options with Web.Contents() only works if the first parameter passed to Web.Contents() is a url that itself can be called on its own without an error.
Determine whether your dynamic data source can be refreshed
Open the Data Source Settings dialog in Power Query Editor, and then select Data Sources In Current File. In the window that appears, look for the following warning message, as shown in the following image:
Some data sources may not be listed because of hand-authored queries.
For me, the specific line i was running into issues with is this:
Web.Contents("https://communications.contoso.com/ssv3/odata/SentMessages(" & Text.From([ID]) & ")/ContactReads")
letSource = 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"
I was fortunate enough to get some assistance from Chris Webb himself! Using the RelativePath (didn’t need to use the Query option) I was able to successfully set up a scheduled refresh on this dataset with a dynamic source! 🎉🎊🥳
Web.Contents("https://communications.contoso.com ", [RelativePath=" ssv3/odata/SentMessages(" & Text.From([ID]) & ")/ContactReads"])