XML to table - seperate columns and rows

[Anonymous]

I have succesfully run a Soap API call to retrieve XML data however I am unable to parse the data into readable tables due to the column names data being seperate from the row data (as per below)

The below screenshot is as far as I get - as when I expand the "Row" column it duplicates every item

After expanding twice. As you can see the second column doesnt match the first column 

[Icey]

Hi @Anonymous ,

Is this problem sloved?

If it is sloved, could you kindly accept it as a solution to close this case?  

If not, please let me know.  

Best Regards

Icey

[Anonymous]

Hi @Icey  although it looks like some variation of the answers in your links could work, I was unable to manipulate their work to solve mine 

In the end I expanded only one side of the tables, pivotted and added new columns to match the column names I didnt expand

I will mark your reply as solved though as I suspect the issue is with my lack of power BI knowledge

[MrSan]

Hello,

Any chance do you show us how are you connecting Power BI with the SOAP API?

[Anonymous]

Hey @MrSan 

I used the answer from dataloreous in the below link

https://community.powerbi.com/t5/Desktop/How-to-get-data-from-a-SOAP-WS-with-Power-BI/td-p/29040

I used the below with the code, with the xml body stored in an XML file 

let
Source = Xml.Tables(Web.Contents("API WEB PAGE",[Content=File.Contents("XML FILE LOCATION"),Headers=[#"Accept-Encoding"="gzip,deflate", SOAPAction="", #"Content-Type"="text/xml;charset=UTF-8"]])),

[Icey · Accepted solution]

Hi @Anonymous ,

Maybe you can refer to this post: Expand Column Containing Some Tables and this blog:  Expanding All Columns In A Table In Power Query.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.