Forum Discussion

ben_faulkner31's avatar
ben_faulkner31
Regular Visitor
2 years ago
Solved

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 com...
Need Help
  • ben_faulkner31's avatar
    ben_faulkner31
    2 years ago

    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

ben_faulkner31's avatar
ben_faulkner31
Regular Visitor
2 years ago

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's avatar
    lbendlin
    Super User
    2 years ago

    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's avatar
      ben_faulkner31
      Regular Visitor
      2 years ago

      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's avatar
        lbendlin
        Super User
        2 years ago

        how many columns does your GetComments function actually return?