Power BI Service - SharePoint Lists - This dataset includes a dynamic data source ERROR

[ben_faulkner31]

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

[ben_faulkner31 · Accepted 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

[v-xinruzhu-msft]

Hi @ben_faulkner31 

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

[lbendlin]

you must use RelativePath .

Web.Contents - PowerQuery M | Microsoft Learn

[ben_faulkner31]

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"

[ben_faulkner31]

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

[lbendlin]

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)

[ben_faulkner31]

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 🙂

[lbendlin]

how many columns does your GetComments function actually return?

[ben_faulkner31]

Hi @lbendlin 

Returns 16 in total

[lbendlin]

but you are only using three of them.  Change the SharePoint query or use a view.

[ben_faulkner31]

@lbendlin Thanks for your help - all sorted 🙂

[ben_faulkner31]

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