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 30,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/
7 Comments
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Great great posts. Thank you
Hi Parker, these videos have been very useful. However I’m facing some trouble when trying to configurate a gateway for an automatic update, I’m getting “Web.Contents failed to get contents from ‘https://api.powerbi.com/v1.0/myorg/’ (404):” do you know how to fix this?
Hi Parker!
I really enjoyed all four parts of your Power BI Admin View video series, but I can’t seem to find the blog post that ties them all together. Am I looking in the wrong place?
Thanks!
James
Hi Parker and the BI Elite Team,
this is a very useful tutorial! I’ve been working through adapting this code to a REST API with a similar NextToken paging mechanism. When I invoke the function however, I get a cyclic reference error when I invoke the custom function with some neutral starting parameters. Will the function only work when called from a completely separate query where it is wrapped in another statement or should the custom function be invokable on its own and return a combined table?
Regards,
Henrik
HI Parker, This was a great series. I was able to replicate everything, however I did get MFA error while sending Access Token Request. I then had to disable ‘Enable Security defaults’ from the Active Directory to be able to run it successfully. Although I was using a test environment, so disabling ‘Enable Security defaults’ was an option but for actual tenant its enabled (MFA). Is there a way around to be able Call Admin APIs?
Hi Parker, thanks for videos, without them I wouldn’t be able to understand the APIs. For Line 16 of the Activity Query, not all fields returned by the api are included. There are some missing fields like the DashboardID and DashboardName. This 2 fields are needed to check the activity on a dashboard. Thk u once again!
Thanks for this wonderful solution.. xample