The Power BI Power Query M to fetch the Azure DevOps Pull Requests via its REST API. This is the source code for my technical blog post. For detail explanation, please visit: https://note.datengineer.dev/posts/how-to-create-azure-devops-pull-requests-reporting-with-power-bi/

Azure DevOps PRs.m

let
    Source = Json.Document(Web.Contents("https://dev.azure.com/"&_organization&"/"&_project&"/_apis/git/pullrequests?searchCriteria.includeLinks=true&searchCriteria.status=all&$top="&_top&"&api-version=7.1-preview.1")),
    #"Converted to Table" = Table.FromRecords({Source}, {"value"}),
    #"Expanded value" = Table.ExpandListColumn(#"Converted to Table", "value"),
    #"Expanded value1" = Table.ExpandRecordColumn(
        #"Expanded value",
        "value",
        {"repository", "pullRequestId", "codeReviewId", "status", "createdBy", "creationDate", "closedDate", "title", "description", "sourceRefName", "targetRefName", "mergeStatus", "isDraft", "mergeId", "reviewers", "labels", "url", "completionOptions", "supportsIterations", "completionQueueTime"},
        {"value.repository", "value.pullRequestId", "value.codeReviewId", "value.status", "value.createdBy", "value.creationDate", "value.closedDate", "value.title", "value.description", "value.sourceRefName", "value.targetRefName", "value.mergeStatus", "value.isDraft", "value.mergeId", "value.reviewers", "value.labels", "value.url", "value.completionOptions", "value.supportsIterations", "value.completionQueueTime"}
    ),
    #"Expanded value.repository" = Table.ExpandRecordColumn(#"Expanded value1", "value.repository", {"name"}, {"value.repository.name"}),
    #"Expanded value.createdBy" = Table.ExpandRecordColumn(#"Expanded value.repository", "value.createdBy", {"displayName", "id", "uniqueName"}, {"value.createdBy.displayName", "value.createdBy.id", "value.createdBy.uniqueName"}),
    #"Expanded value.completionOptions" = Table.ExpandRecordColumn(#"Expanded value.createdBy", "value.completionOptions", {"mergeCommitMessage", "mergeStrategy", "transitionWorkItems"}, {"value.completionOptions.mergeCommitMessage", "value.completionOptions.mergeStrategy", "value.completionOptions.transitionWorkItems"}),
    #"Expanded value.reviewers" = Table.TransformColumns(#"Expanded value.completionOptions", {{"value.reviewers", each Combiner.CombineTextByDelimiter(", ")(List.Transform(_, each [displayName]))}}),
    #"Added iterations" = Table.AddColumn(#"Expanded value.reviewers", "iterations", each Json.Document(Web.Contents([value.url]&"/iterations/"))),
    #"Expanded iterations" = Table.ExpandRecordColumn(#"Added iterations", "iterations", {"count"}, {"iterations.count"}),
    #"Added iterations.changes" = Table.AddColumn(#"Expanded iterations", "iterations.changes", each Json.Document(Web.Contents([value.url]&"/iterations/"&Number.ToText([iterations.count])&"/changes?api-version=7.1-preview.1"))),
    #"Expanded iterations.changes" = Table.ExpandRecordColumn(#"Added iterations.changes", "iterations.changes", {"changeEntries"}, {"iterations.changes.changeEntries"}),
    #"Added iterations.changes.changeEntries.count" = Table.AddColumn(#"Expanded iterations.changes", "iterations.changes.changeEntries.count", each List.Count([iterations.changes.changeEntries])),
    #"Removed iterations.changes.changeEntries" = Table.RemoveColumns(#"Added iterations.changes.changeEntries.count",{"iterations.changes.changeEntries"}),
    #"Added threads" = Table.AddColumn(#"Removed iterations.changes.changeEntries", "threads", each Json.Document(Web.Contents([value.url]&"/threads?api-version=7.1-preview.1"))),
    #"Expanded threads" = Table.ExpandRecordColumn(#"Added threads", "threads", {"value"}, {"threads.value"}),
    #"Added threads.value.commentCount" = Table.AddColumn(#"Expanded threads", "threads.value.commentCount", each List.Sum(List.Transform([threads.value], each Number.From(Record.HasFields(_, "status"))))),
    #"Added threads.value.firstApprovalTime" = Table.AddColumn(
        #"Added threads.value.commentCount",
        "threads.value.firstApprovalTime",
        each List.Min(
            List.Transform(
                [threads.value],
                each if
                    Record.HasFields(_[properties], "CodeReviewThreadType") and Record.Field(_[properties][CodeReviewThreadType], "$value") = "VoteUpdate"
                    and Record.HasFields(_[properties], "CodeReviewVoteResult") and Number.FromText(Record.Field(_[properties][CodeReviewVoteResult], "$value")) > 0
                then _[publishedDate]
                else null
            )
        )
    ),
    #"Removed threads.value" = Table.RemoveColumns(#"Added threads.value.firstApprovalTime",{"threads.value"}),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Removed threads.value",
        {{"value.repository.name", type text}, {"value.pullRequestId", Int64.Type}, {"value.codeReviewId", Int64.Type}, {"value.status", type text}, {"value.createdBy.displayName", type text}, {"value.createdBy.id", type text}, {"value.createdBy.uniqueName", type text}, {"value.creationDate", type datetime}, {"value.closedDate", type datetime}, {"value.title", type text}, {"value.description", type text}, {"value.sourceRefName", type text}, {"value.targetRefName", type text}, {"value.mergeStatus", type text}, {"value.isDraft", type logical}, {"value.mergeId", type text}, {"value.reviewers", type text}, {"value.labels", type text}, {"value.url", type text}, {"value.completionOptions.mergeCommitMessage", type text}, {"value.completionOptions.mergeStrategy", type text}, {"value.completionOptions.transitionWorkItems", type text}, {"value.supportsIterations", type logical}, {"value.completionQueueTime", type datetime}, {"iterations.count", Int64.Type}, {"iterations.changes.changeEntries.count", Int64.Type}, {"threads.value.commentCount", Int64.Type}, {"threads.value.firstApprovalTime", type datetime}}
    )
in
    #"Changed Type"