Forum Discussion
Power BI Service - SharePoint Lists - This dataset includes a dynamic data source ERROR
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
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?
Hi lbendlin
Returns 16 in total
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