Dynamic Dataset refresh error

[hjoshi4u26]

I have a list of Pull Request IDs from which I have to get the data via REST APIs. I am able to fetch the data locally, but when I publish it to PowerBI Service, I face the error "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. Learn more: https://aka.ms/dynamic-data-sources."

Sharing below the M-Code:
let
Source = SharePoint.Files("https://*******.sharepoint.com/teams/*****", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Pull Request List Sample.xlsx")),
#"Pull Request List Sample xlsx_https://****** sharepoint com/teams/*********/Shared Documents/General/*************" = #"Filtered Rows"{[Name="Pull Request List Sample.xlsx",#"Folder Path"="https://**********.sharepoint.com/teams/*********/Shared Documents/General/********"]}[Content],
#"Imported Excel Workbook" = Excel.Workbook(#"Pull Request List Sample xlsx_https://******* sharepoint com/teams/********/Shared Documents/General/*********"),
PR_Table = #"Imported Excel Workbook"{[Item="PR",Kind="Table"]}[Data],
#"Changed Type2" = Table.TransformColumnTypes(PR_Table,{{"Pull Request Id", type text}}),
#"Pull Request API" = Table.AddColumn(#"Changed Type2", "Pull Request API", each "https://*************.com/******/_apis/git/pullrequests/"&[Pull Request Id]&"?api-version=7.0", Text.Type),
#"Transformed to Binary" = Table.TransformColumns(#"Pull Request API", {{"Pull Request API", VSTS.Contents}}),
#"Transformed to Json" = Table.TransformColumns(#"Transformed to Binary", {{"Pull Request API", Json.Document}}),
#"Expanded Pull Request API" = Table.ExpandRecordColumn(#"Transformed to Json", "Pull Request API", {"closedDate","closedBy"}, {"Closed Date", "Closed By"}),
#"Expanded Closed By" = Table.ExpandRecordColumn(#"Expanded Pull Request API", "Closed By", {"displayName"}, {"Closed By"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Closed By",{{"Closed By", type text}, {"Title", type text}})
in
#"Changed Type1"

.

.

Note: Pull Request Id data type is Text so used '&' to combine it to create API URL. But facing the error as mentioned.

I have read Chris Webbs Blog but did not understand it properly.

Can anyone please let me know how can I solve this error..? Any help would be much appreciated. Thanks in advabce 🙂

[hjoshi4u26 · Accepted solution]

No worries. I have done it. Sharing the steps below. Initially I was doing below steps to get REST API Output in Json Records:-->

#"Pull Request API" = Table.AddColumn(#"Changed Type2", "Pull Request API", each "https://*************.com/******/_apis/git/pullrequests/"&[Pull Request Id]&"?api-version=7.0", Text.Type),
#"Transformed to Binary" = Table.TransformColumns(#"Pull Request API", {{"Pull Request API", VSTS.Contents}}),
#"Transformed to Json" = Table.TransformColumns(#"Transformed to Binary", {{"Pull Request API", Json.Document}}),

.

But instead I did this:-->
#"Pull Request API" = Table.AddColumn(#"Changed Type2", "Pull Request API",each
     Json.Document(
          VSTS.Contents(

               "https://************.com/**********/_apis/git/pullrequests/",

               [RelativePath = [Pull Request Id]&"?api-version=7.0"]

          )

     )
),

.

This solved my problem. I watched a youtube video which explains very easily how to do it, sharing the link below.

https://youtu.be/sv_upbpq_Cw