Get title from Azure DevOps 3 leves above

[Jan_DK]

Hi

In Azure DevOps we are having a structure like this

Project 'ProjectTitle'

     Epic

        Feature

             Product Backlog Item 'PBITitle', 'PBIDescription'

I use Odata to grab data from WorkItems in Azure DevOps.

I would like to make a table that give's me the 'ProjectTitle', PBITitle', 'PBIDescription' in the same row as 3 diffent colums.

How do i do this?

Thanks ;o)

Br. Jan

[v-fenling-msft]

Hi, @Jan_DK 

I am glad to help you.   

According to your description, you want to get title from Azure DevOps 3 leves above? 

If I understand you correctly, then you can refer to my solution.  

Since you didn't give me specific datasets to test, I assumed two scenarios myself and created some simple datasets to test, so I hope this helps. 

First scenario: 

First is to assume that ProjectTitle, PBITitle, PBIDescription are located in different tables: 

Then they should have a correspondence and the steps to implement this are as follows: 

After connecting to the data source, select the Projects table in the Power Query Editor and click Merge Queries： 

After this step, click Expand Projects to get ProjectTitle, PBITitle, PBIDescription in the same row in different columns： 

Second scenario: 

Suppose your dataset is in this form: 

Then you need to perform pivot operations, the specific steps are as follows: 

First you need to add the index column: 

Then select Item column and click Pivot Column: 

After this step you can get ProjectTitle, PBITitle, PBIDescription in the same row is located in a different column： 

I hope these suggestions are helpful. If not, please share some data that has the same structure as your dataset. Please note that this data must not contain sensitive or private information. We will do our best to help you solve the problems you are experiencing. 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jan_DK]

 Hi Fen Ling,

Thanks for reply, you almost read my mind 🐵

A bit more information

My masterdata is in the same table but mixed in the same colums.

See example file attached where the tabel looks like this

And i would like it to look like this where there mig should be a new coloum defined i guess?

Example file 

[v-fenling-msft · Accepted solution]

Hi, @Jan_DK 

Yes, you need to New Column. you can refer to my DAX formula:

New Column to be define =
SWITCH (
    TRUE (),
    'WorkItems'[Title] = "My first PBI", "My first project",
    'WorkItems'[Title] = "My second PBI", "My second project",
    BLANK ()
)

This is the result:

I hope it will help you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jan_DK]

Thanks 🐵