(Ideally, I would like to do this as a Power BI custom data connector. I have tried, for some reason, my Visual Studio has been crashing when I click on connect to PBI Service using OAuth2. Once I succeed, it would become another blog post)

Why Calling Power BI service using Power BI Desktop?

We all know Power BI is a great tool and easily adaptable. Using Power BI Desktop and service, we can create models then we can publish or save them to Power BI Service. In service, we have workspaces, dashboards, reports, datasets, workbooks and apps; Power BI users create and/or access all these features based on their access levels. Which means all your users will be creating and/or accessing content from Power BI service. Which is great, but how can I answer questions like below:

• Number of workspaces
• Who has access to that particular workspace and their level of access
• Number of Dashboards
• Number of Reports
• Dataset Last Refresh time
• Average refresh time of X data set? ( You can plan, do you need premium or not based on this value)
• How many Gateways ( For me, it’s not working at the moment)

And much more

Of course, I can get this information by going to Power BI service but how about getting answers to all those questions in one report.With extensive Power BI documentation, we know we could use Power BI API to Embedded Power BI reports or do data refresh. So I thought to call same Power BI API using Power BI Desktop to get the information I need for documenting my Power BI.

Calling Power BI service using Power BI Desktop

Before we dwell into this, let’s understand my approach for API Access. So when using Power BI API, the user will be able to see whatever they have access to. When a user creates a workspace in Power BI mean they are creating an office 365 group, which means they are creating loads of things in Office 365. I will talk more about this in another blog post. For now, just assume I have an SA User. The only user who can create workspaces, which means this user has access to every single workspace in my Power BI tenant. Instead of using an OAuth authentication, I am using the hard coded username and password for my API calls. As I mentioned earlier, I will talk more about Power BI workspaces and my so-called SA User in another blog post (Power BI publishing Strategy)

To call Power BI API, first I need to register an application for Power BI. You can create an application using this URL – https://dev.powerbi.com/apps. You can find more information about how to create and edit an App here – https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-register-app/.

Don’t forget to save your Client ID and Client Secret safe; I still don’t know how to find client secret using Azure Portal (Maybe I have not looked well enough!)

Then in Power BI Desktop, I created two parameters one for Client ID and one for Client Secret. Next, I created a query called getToken with following M

GetToken:

let
    //App Details
    client_id = ClientID,
    client_secret=ClientSecret,

    //Authentication
    
    //URI's
    redirect_uri = "http://localhost:13526/redirect",
    token_uri = "https://login.windows.net/common/oauth2/token", 
    authorize_uri = "https://login.windows.net/common/oauth2/authorize",
    resource="https://analysis.windows.net/powerbi/api",
    
    //User Details  
    username="xxxxxxxxxxxxxxx.com",
    password="xxxxxxxxx",
    

   tokenResponse = Json.Document(Web.Contents(token_uri,          
                                [
                                    Content=
                                    Text.ToBinary(Uri.BuildQueryString(
                                    [
                                        client_id = client_id
                                        ,client_secret=client_secret
                                        ,username=username
                                        ,password=password
                                        ,resource=resource
                                        ,grant_type = "password"
                                        ,redirect_uri = redirect_uri
                                    ]))
                                    ,Headers=
                                        [Accept="application/json"]
                                        , ManualStatusHandling={400}
                                ])),
                                access_token = tokenResponse[access_token],
    token = tokenResponse[access_token]
in
    token

By using above query, I get an access token to call Power BI API using the given user access details. Then it’s a matter of calling various Power BI API GET operations. There is a plenty of documentation available, but Power BI Apiary was most useful one for me. By going to http://docs.powerbi.apiary.io/#introduction/what-can-i-do-with-the-power-bi-api? You can quickly see different operations you could do with Power BI API. I want to start with workspaces.

List all Groups / List all Workspaces:

Once up on time Workspaces were called Groups, may be that’s why in API they are still called Groups. The GET call for Groups is https://api.powerbi.com/v1.0/myorg/groups. I created a query called Workspaces with following M Code to get workspaces data.

let
    getgroups_uri ="https://api.powerbi.com/v1.0/myorg/groups",
    GetGroups = Json.Document(Web.Contents(getgroups_uri, 
                [
                Headers=
                    [
                    Authorization="Bearer "& refToken
                    ], 
                ManualStatusHandling={400}
                ]
                )),
    value = GetGroups[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "isReadOnly", "name"}, {"id", "isReadOnly", "name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"id", "Workspace ID"}, {"name", "Workspace name"}})
in
    #"Renamed Columns"

List all Datasets:

Next all datasets under each workspace. Datasets GET request need GroupID query parameter. Hence I created a function called fnGetdatasets with following M Code

(groupid as text)=>

let
getdatasets_uri =" https://api.powerbi.com/v1.0/myorg/groups/"&groupid&"/datasets",

 GetGroups = Json.Document(Web.Contents(getdatasets_uri ,

 [

 Headers=

 [

 Authorization="Bearer "&getToken

 ],

 ManualStatusHandling={400}

 ]

 )),

 value = GetGroups[value],

 #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

 #"Converted to Table"

Then created a new query with following M code to get all datasets under each workspace.

let

 Source = Workspaces,

 #"Invoked Custom Function" = Table.AddColumn(Source, "fnDatasets", each fnDatasets([id])),

 #"Expanded fnDatasets" = Table.ExpandTableColumn(#"Invoked Custom Function", "fnDatasets", {"Column1"}, {"Column1"}),

 #"Expanded Column1" = Table.ExpandRecordColumn(#"Expanded fnDatasets", "Column1", {"id", "name", "addRowsAPIEnabled", "configuredBy", "isRefreshable", "isEffectiveIdentityRequired", "isEffectiveIdentityRolesRequired", "isOnPremGatewayRequired"}, {"id.1", "name.1", "addRowsAPIEnabled", "configuredBy", "isRefreshable", "isEffectiveIdentityRequired", "isEffectiveIdentityRolesRequired", "isOnPremGatewayRequired"})

in

 #"Expanded Column1"

All Reports:

Again Reports API needs Workspace ID to get reports. There are two ways you can get Reports data. However, I went for the one who needs GroupID. I created function and then called that function for each GroupID

fnReports Function:

(groupid as text)=>
let
getdatasets_uri ="https://api.powerbi.com/v1.0/myorg/groups/"&groupid&"/reports",
GetGroups = Json.Document(Web.Contents(getdatasets_uri , 
[
Headers=
[
Authorization="Bearer "&refToken
], 
ManualStatusHandling={400}
]
)),
value = GetGroups[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

Reports Query:

let
getgroups_uri ="https://api.powerbi.com/v1.0/myorg/groups",
GetGroups = Json.Document(Web.Contents(getgroups_uri, 
[
Headers=
[
Authorization="Bearer "& refToken
], 
ManualStatusHandling={400}
]
)),
value = GetGroups[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "isReadOnly", "name"}, {"id", "isReadOnly", "name"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"id", "Workspace ID"}, {"name", "Workspace name"}}),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "fnReports", each fnReports([Workspace ID])),
#"Expanded fnReports" = Table.ExpandTableColumn(#"Invoked Custom Function", "fnReports", {"Column1"}, {"Column1"}),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded fnReports", "Column1", {"id", "modelId", "name", "webUrl", "embedUrl", "isOwnedByMe", "isOriginalPbixReport", "datasetId"}, {"Report.id", "Report.modelId", "Report.name", "Report.webUrl", "Report.embedUrl", "Report.isOwnedByMe", "Report.isOriginalPbixReport", "Report.datasetId"})
in
#"Expanded Column2"

Like this, I can also get List of users under each workspace, Dataset refresh history. In the documentation I can see, I can get all gateways and much more information about Gateways – https://msdn.microsoft.com/en-us/library/mt809129.aspx. I think this feature is not available yet, as it is not shown in the apiary and I get an error when I call this service.(Something for future!)

A sample report below:

Summary:

I have to admit, it was not easy. The main difficulty for me was too much information out there. I struggled to understand which API to use, what redirect URL and headers etc. I don’t know much about API’s. All these headers and scopes are very new to me; I still don’t understand why or how they work. So part of the problem was me and part was various documentation about Power BI Embedded. However, at the end of the day, I learned something new. By calling API, I can answer many questions with one refresh in Power BI Desktop.

This post is already too long; I will create a nicer looking template with all queries and post it soon. It’s pretty difficult to get much time to blog during school summer holidays. I hope I create a template soon and also hope this gives some ideas and help someone out there.

Some References for Power BI API :

https://msdn.microsoft.com/en-us/library/mt147898.aspx

http://docs.powerbi.apiary.io/#introduction/what-can-i-do-with-the-power-bi-api?

https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-get-azuread-access-token/

Till next time,

Prathy 🙂