Converting XML data to a table

[Anonymous]

Hello all,

I am trying to reach out to our organisation's HR data and shape the data on PowerBI Desktop.

We are connecting through API and the data structure is XML.

When I connect to the directory, data is visible as below:

When I open the query editor, what I see is below (as the next step I am expanding the tables to see the data):

After expanding the tables; now I see:

However, the data structure I need  is like the following:

displayName	firstName	lastName	jobTitle
John Doe	John	Doe	Expert
Jane Doe	Jane	Doe	Lead

I tried to convert data using "transpose" in the query, but it didn't work..

You guys have any idea on how to shape the data in a usable format?

Thanks in advance for your support.

Ugur

[Anonymous · Accepted solution]

@Anonymous,

You can pivot Attribute::ID column, select Element::Text column as your Values Column, and under Advanced Options, choose Don't Aggregate. 

[Anonymous]

@Anonymous Thank you! This is what I've been looking for!