cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
P3UAL Frequent Visitor
Frequent Visitor

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

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".

edit credentials.png
 

 


 

 

 

 

 

 

 

 

 

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
P3UAL Frequent Visitor
Frequent Visitor

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

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"
2 REPLIES 2
P3UAL Frequent Visitor
Frequent Visitor

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

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"
Moderator v-yuezhe-msft
Moderator

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

@P3UAL,

Thanks for your sharing.Smiley Happy

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.