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"

Parker Stevens

Parker is the creator of the BI Elite YouTube channel, a community of over 20,000 students learning Power BI, DAX, and Power Query. He is a Microsoft Data Platform MVP for his work with Power BI and continues to provide elite Power Platform training courses to help data analysts, BI developers, and citizen dashboard creators reach their goals. Parker is committed to producing high-quality training content that is also extremely cost-effective, to ensure that the largest amount of users can benefit from the content. Training courses located at https://training.bielite.com/