Forum Discussion
Anonymous
6 years agoNot applicable
Need help building query with dynamic data source to set with scheduled refresh
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"
- https://sqlserverbi.blog/2018/10/21/web-api-data-sources-with-power-query-and-scheduling-data-refresh-in-the-power-bi-service/
- https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/
- https://blog.crossjoin.co.uk/2019/04/25/skip-test-connection-power-bi-refresh-failures/
- https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/
- 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
- Anonymous6 years ago
Web.Contents("https://communications.contoso.com ", [RelativePath=" ssv3/odata/SentMessages(" & Text.From([ID]) & ")/ContactReads"])was able to get it with the above ^
2 Replies
- AnonymousNot applicable
anyone? 😞 😫
- AnonymousNot applicable
Web.Contents("https://communications.contoso.com ", [RelativePath=" ssv3/odata/SentMessages(" & Text.From([ID]) & ")/ContactReads"])was able to get it with the above ^