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

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"