Forum Discussion
Power BI Service - SharePoint Lists - This dataset includes a dynamic data source ERROR
Hi there, I've created a Power BI report using a SharePoint List as the data source. In order to get the comments on List items into Power BI, I've used a Power Query function I found here on the community (code at the bottom).
The report works all fine in desktop but when published to Power BI Service, I get the following error when attempting to refresh the data: This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed.
I believe this is caused by part of the fnGetComments Power Query code as it's the only dynamic data I'm importing with the itemId...
I've seen a few solutions on here for external application data sources getting the same error, and tried adding in the RelativePath method in several ways but nothing seems to work... I can't find anything on this error specific to SharePoint Lists so wonder whether anyone has faced this issue/been able to solve this before?
Any help would be greatly appreciated 🙂
Code as follows:
let
GetComments = (itemId as number) as table =>
let
// Hardcoded SharePoint site URL and list name
siteUrl ="https://[HIDDEN FOR SECURITY]",
listName = "EAM Task Tracker",
// Construct the endpoint URL
commentsEndpoint = siteUrl & "/_api/web/lists/getbytitle('" & listName & "')/items(" & Text.From(itemId) & ")/Comments()",
// Make the HTTP request
Source = Json.Document(Web.Contents(commentsEndpoint, [Headers=[#"Accept"="application/json;odata=verbose", #"Content-Type"="application/json;odata=verbose"]])),
// Parse the JSON response
Data = Source[d],
Results = Data[results],
ConvertedToTable = Table.FromRecords(Results)
in
ConvertedToTable
in
GetComments
For anyone else coming across this, I managed to get it working using the following code. Tested in Power BI Service and no longer get the data refresh error 🙂
let
fnGetComments = (itemId as number) as table =>
let
// Construct the dynamic portion of the URL
RelativePath = "/_api/web/lists/getbytitle('YOUR LIST')/items(" & Number.ToText(itemId) & ")/Comments()",
// Basic Web Request with RelativePath
Source = Json.Document(
Web.Contents(
"https://dnastream.sharepoint.com/sites/YOURTEAM",
[RelativePath = RelativePath,
Headers = [#"Accept"="application/json;odata=verbose", #"Content-Type"="application/json;odata=verbose"]]
)
),
// JSON Parsing
JsonData = Source[d],
// Table Transformation
Results = JsonData[results],
ConvertedToTable = Table.FromRecords(Results),
// Return Statement
OutputTable = ConvertedToTable
in
OutputTable
in
fnGetComments
12 Replies
Hi lbendlin, thanks for your response.
Forgive me as I'm not massively technical... I've added it in as per the below but now I'm getting the following error: Expression.Error: 3 arguments were passed to a function which expects between 1 and 2.
Details:
Pattern=
Arguments=[List]Could you please assist?
I've changed my code and constructed the URL in one line now as per the below:
let
GetComments = (itemId as number) as table =>
let
// Hardcoded SharePoint site URL and list name
siteUrl ="https://HIDDEN FOR SECURIT",
listName = "EAM Task Tracker",// Construct the URL and make the HTTP request
Source = Json.Document(
Web.Contents(
siteUrl & "/_api/web/lists/getbytitle('" & listName & "')/items(", [RelativePath = Text.From(itemId) & ")/Comments()"],
[Headers=[#"Accept"="application/json;odata=verbose", #"Content-Type"="application/json;odata=verbose"]]
)
),
// Parse the JSON response
// Adjust the path based on the actual structure of the JSON response
Data = Source[d],
Results = Data[results],
ConvertedToTable = Table.FromRecords(Results)
in
ConvertedToTable
in
GetCommentsYour _api url component needs to all go into the RelativePath part. The URL must be constant, and it must be recognizable as a valid URL (ie return a 200 code)
This is the code I'm using to call it that I'm getting the 3 arguments error on:
let
Source = SharePoint.Tables("https://dnastream.sharepoint.com/sites/UltimoTeam", [ApiVersion = 15]),
#"1603f1fd-290a-44f8-9ec4-222c86252266" = Source{[Id="1603f1fd-290a-44f8-9ec4-222c86252266"]}[Items],
#"Removed Other Columns" = Table.SelectColumns(#"1603f1fd-290a-44f8-9ec4-222c86252266",{"Id"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each #"fnGetComments"([Id])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"author", "createdDate", "text"}, {"Custom.author", "Custom.createdDate", "Custom.text"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom.createdDate", type datetime}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Custom.createdDate", Order.Descending}}),
#"Expanded Custom.author" = Table.ExpandRecordColumn(#"Sorted Rows", "Custom.author", {"email"}, {"Custom.author.email"})
in
#"Expanded Custom.author"
Whether the solution lbendlin provided helps you solve the problem? if the solution helps, please consider to accept it as a solution.
Best Regards!
Yolo Zhu
For anyone else coming across this, I managed to get it working using the following code. Tested in Power BI Service and no longer get the data refresh error 🙂
let
fnGetComments = (itemId as number) as table =>
let
// Construct the dynamic portion of the URL
RelativePath = "/_api/web/lists/getbytitle('YOUR LIST')/items(" & Number.ToText(itemId) & ")/Comments()",
// Basic Web Request with RelativePath
Source = Json.Document(
Web.Contents(
"https://dnastream.sharepoint.com/sites/YOURTEAM",
[RelativePath = RelativePath,
Headers = [#"Accept"="application/json;odata=verbose", #"Content-Type"="application/json;odata=verbose"]]
)
),
// JSON Parsing
JsonData = Source[d],
// Table Transformation
Results = JsonData[results],
ConvertedToTable = Table.FromRecords(Results),
// Return Statement
OutputTable = ConvertedToTable
in
OutputTable
in
fnGetComments
