cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ugurgulluev Regular Visitor
Regular Visitor

Converting XML data to a table

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:

1.jpg

 

2.jpg

 

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

3.jpg

 

After expanding the tables; now I see:

4.jpg

 

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

 

displayNamefirstNamelastNamejobTitle
John DoeJohnDoeExpert
Jane DoeJaneDoeLead

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
NickNg278 Senior Member
Senior Member

Re: Converting XML data to a table

@ugurgulluev,

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

2 REPLIES 2
NickNg278 Senior Member
Senior Member

Re: Converting XML data to a table

@ugurgulluev,

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

ugurgulluev Regular Visitor
Regular Visitor

Re: Converting XML data to a table

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