Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ben_faulkner31
Regular Visitor

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

1 ACCEPTED SOLUTION
ben_faulkner31
Regular Visitor

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

View solution in original post

12 REPLIES 12
ben_faulkner31
Regular Visitor

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
Community Support
Community Support

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
Super User
Super User

you must use RelativePath .

 

Web.Contents - PowerQuery M | Microsoft Learn

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?

Hi @lbendlin 

Returns 16 in total

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

@lbendlin Thanks for your help - all sorted 🙂

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors