Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
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
Solved! Go to Solution.
Web.Contents("https://communications.contoso.com ", [RelativePath=" ssv3/odata/SentMessages(" & Text.From([ID]) & ")/ContactReads"])
was able to get it with the above ^
anyone? 😞 😫
Web.Contents("https://communications.contoso.com ", [RelativePath=" ssv3/odata/SentMessages(" & Text.From([ID]) & ")/ContactReads"])
was able to get it with the above ^
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.