Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
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
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
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
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"
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
GetComments
Your _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)
Forgive me as I'm not massively technical...
I've changed the code as per the below:
let
GetComments = (itemId as number) as table =>
let
// Make the HTTP request
Source = Json.Document(
Web.Contents(
"https://dnastream.sharepoint.com/sites/UltimoTeam/", [RelativePath = "_api/web/lists/getbytitle('EAM Task Tracker')/items(" & 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
GetComments
And when I call this function with the following code:
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"
I'm still getting the following error: Expression.Error: 3 arguments were passed to a function which expects between 1 and 2.
Details:
Pattern=
Arguments=[List]
Does a change need to be made to the code that calls the function?
Thanks for your help 🙂
how many columns does your GetComments function actually return?
but you are only using three of them. Change the SharePoint query or use a view.
Apologies @lbendlin, my mistake...
My getAllComments query (which calls my fnGetComments function) outputs 3 columns: Custom.author.email, Custom.createdDate and Custom.text
The getAllComments query filters out all the rest
I'm not familiar with this language - found the solution on here so unsure of how to tailor it to get rid of the error
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
64 | |
27 | |
18 | |
13 |