Tip for anyone trying to connect PBI Desktop to VSTS REST API

[P3UAL]

After struggling for months trying to use Power Bi to extract a VSTS user list (UserEntitlements) along with their subscription level with little on the internet. I finally got it working. The difficulty is authentication and Power BI not natively supporting Basic and passing the Authenticatrion Header using the PAT token. Also OAuth2 is a problem here with Power BI still.

If you start of with a blank query, the M code you need is this:

let
Source = Json.Document(Web.Contents("https://<<ACCOUNT NAME>>.vsaex.visualstudio.com/DefaultCollection/_apis/userentitlements?api-version=4.1-preview")),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{1}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "user", "accessLevel", "lastAccessedDate", "projectEntitlements", "extensions", "groupAssignments"}, {"Column1.id", "Column1.user", "Column1.accessLevel", "Column1.lastAccessedDate", "Column1.projectEntitlements", "Column1.extensions", "Column1.groupAssignments"})
in
#"Expanded Column1"

Then edit the Data Source Settings and ensure Basic authentication is select, put in your PAT token for VSTS in the password box, the user name can be left blank.

Refresh your data and that should pull down the data you need.

When you publish to the cloud, ensure your dataset has the PAT token populated when you "Edit Credentials".


 

It doesn't need to go throught the on-premise gateway as VSTS is on the cloud.

[v-yuezhe-msft]

@P3UAL,

Thanks for your sharing.

Regards,
Lydia

[P3UAL · Accepted solution]

I just found an even better way to do this. The trouble with the above implemenation is with the API call - it only returns 100 items and you have to page through it! A bit tricky using Power BI.

Here is some code for another API call to use that will return a user list in CSV. I just used Fiddler to see what call it makes when you click on the "Export Users" functionality on VSTS.

let
    Source = (Web.Contents("https://<<ACCOUNT NAME>>.vsaex.visualstudio.com/DefaultCollection/_apis/MEMInternal/Export?api-version=3.1-preview")),
    #"Imported CSV" = Csv.Document(Source,[Delimiter=",", Columns=6, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Username", type text}, {"Access Level", type text}, {"Last Access", type text}, {"Test Manager", Int64.Type}, {"Package Management", Int64.Type}})
in
    #"Changed Type"