Where to save VSO Query and How to Connect to VSO Query to build our reports and chats

[SantoshKumar]

Hi Team, 

Below is my requirement:

1. I have created few VSO query as per my requirement.

2. i need to load those queries to my power bi

3. Need to make reports and charts based on the retrieved data.

I have followed below steps:

1. Get Data -> Online Services->Azure DevOps(Beta)->Connect

2. It promted the dialogue box asking for details such as:

 a) asking to provide: Organization which i gave as "microsoft.visualstudio.com"

 b) Team Project: "OS"

3. I was able to connect and it open few queries (not my requirement).

I got stuck at this point , since i need to add my query to connect and requesting to provide some inputs related to it.

My path is something like this:

"https://microsoft.visualstudio.com/OS/_queries/query-edit/af6b2556-9f05-41af-89de-723863cec869/"

AzureDevOpsOnce connected shows up data

PFA.

Thanks

Santosh Kumar P

[V-pazhen-msft]

@SantoshKumar 

I  am not authorized to view the path page though, I am kind of confused with your situation. Once you are able to load the tables from the OS project, could you just retrieve the required data and load to power bi to create your report?

Best,
Paul

[SantoshKumar · Accepted solution]

Hey paul,

Thanks for looking into my issue.

I was able to load the tables from the OS project but i guess those are predefined queries from VSO.

User defined queries are not visible (queries which we written and saved (private or shared) once).

But although i got the soltuion but little bit tricky.

I have followed below steps:

1. I have written a query and saved it.

2. there is an option to open in power bi .

3. choose the above option and open it.

4. Go to Edit Queries and advance editor option.

5. Copy the contents from it. which is as below:

 let 
 url = "https://userfeedback.visualstudio.com",
 collection = "",
 project = "UIF",
 team = "UIF Team",
 id = "988c1a9e-2e46-4b09-9c99-2a5b53a49fe5",

 // Create wrapper for VSTS.AccountContents function as a workaround for the static code analysis in Power BI Service.
 contents = (o) => VSTS.AccountContents(
 url,
[
Version = Record.FieldOrDefault(Record.FieldOrDefault(o, "Headers", []), "Referer", ""),
IsRetry = Record.FieldOrDefault(o, "IsRetry", false),
ManualStatusHandling = Record.FieldOrDefault(o, "ManualStatusHandling", {}),
Query = Record.FieldOrDefault(o, "Query", []),
RelativePath = Record.FieldOrDefault(o, "RelativePath", null),
Timeout = Record.FieldOrDefault(o, "Timeout", null)
]),

Source = Functions[WiqlRunFlatWorkItemQueryById](contents, url, [Collection = collection, Project = project, Team = team], id)
in
Source

6. Now open your (original ) Power Bi file.

then got to ->Get data ->More->Online Services->Azure Devops(Beta)->Connect

 Get Data -> Online Services->Azure DevOps(Beta)->Connect

 It promted the dialogue box asking for details such as:

 a) asking to provide: Organization which i gave as "microsoft.visualstudio.com"

 b) Team Project: "XYZ"

7. Once after providing the details, it will open few sample queries -> select any one then after replace the content in it with the copied once.

8. After that you need to create a Function with Name Functions and copy the content available in Function and save it.

9. This will give the results as expected.

Thanks

Santosh