Building a Power BI Admin Report

Complete blog post to be released following the 4th part of the series.

Part 3 Power Query code…

Get Activity Function:

(accessToken as text, optional startDate as text, optional endDate as text, optional continuationToken as text, optional loop as number, optional data as list) =>
let
Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/",
[
RelativePath=if loop = 0
then "admin/activityevents?startDateTime=" & startDate & "&endDateTime=" & endDate
else "admin/activityevents?continuationToken='" & continuationToken & "'", 
Headers=[Authorization="Bearer " & accessToken]
]
)),
token = Source[continuationToken],
currentData = Source[activityEventEntities],
appendedData = List.Combine({data, currentData}),
loopNum = loop + 1,
output = 
if token is null or loopNum > 100
then appendedData
else @#"GET Activity"(accessToken, "", "", token, loopNum, appendedData)
in
output

Activity Query:

let
Source = List.Dates,
#"Invoked FunctionSource" = Source(Date.AddDays(DateTime.Date(DateTime.FixedLocalNow()), -3), 3, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "StartDate", each "'"
& Date.ToText([Date], "YYYY-MM-DD")
& "T00:00:00.000Z"
& "'"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "EndDate", each "'"
& Date.ToText([Date], "YYYY-MM-DD")
& "T23:59:59.999Z"
& "'"),
#"Invoked Custom Function" = Table.AddColumn(#"Added Custom1", "GETActivity", each #"GET Activity"(#"GET Access Token"(), [StartDate], [EndDate], null, 0, {})),
#"Expanded GETActivity" = Table.ExpandListColumn(#"Invoked Custom Function", "GETActivity"),
#"Expanded GETActivity1" = Table.ExpandRecordColumn(#"Expanded GETActivity", "GETActivity", {"Id", "RecordType", "CreationTime", "Operation", "OrganizationId", "UserType", "UserKey", "Workload", "UserId", "ClientIP", "UserAgent", "Activity", "ItemName", "WorkSpaceName", "DatasetName", "ReportName", "WorkspaceId", "ObjectId", "DatasetId", "ReportId", "IsSuccess", "ReportType", "RequestId", "ActivityId", "DistributionMethod", "ImportId", "ImportSource", "ImportType", "ImportDisplayName"}, {"Id", "RecordType", "CreationTime", "Operation", "OrganizationId", "UserType", "UserKey", "Workload", "UserId", "ClientIP", "UserAgent", "Activity", "ItemName", "WorkSpaceName", "DatasetName", "ReportName", "WorkspaceId", "ObjectId", "DatasetId", "ReportId", "IsSuccess", "ReportType", "RequestId", "ActivityId", "DistributionMethod", "ImportId", "ImportSource", "ImportType", "ImportDisplayName"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded GETActivity1", each ([Id] <> null) and ([Operation] <> "ExportActivityEvents" and [Operation] <> "GetGroupsAsAdmin"))
in #"Filtered Rows"