Power Query - API

[yanika_a]

Hi all, 

I am using a Power Query to query an API (Xero API) but I am struggling with the transformation of data resulting from the API GET request to a table format. Details below; 

This is my Query; 

let
AccessToken = "Bearer xxxxxxxxxxxxxxxxxxxxxxxxxxxx",
 Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://api.xero.com/api.xro/2.0/Contacts", [Headers=[#"Content-Type"="application/json", #"xero-tenant-id"="xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", #"Accept"= "application/json", Authorization=AccessToken]]), null, null, 65001)}),
#"Parsed XML" = Table.TransformColumns(Source,{},Xml.Tables)
in
#"Parsed XML""

Source returns the following result, all in 1 Column ; 

Full xml;

"<Response xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">"
  <Id>78e84298-d216-4e81-bd85-a86dfacdea98</Id>
  <Status>OK</Status>
  <ProviderName>PurchInvAutomation</ProviderName>
  <DateTimeUTC>2021-11-18T08:38:07.466739Z</DateTimeUTC>
  <Contacts>
	<Contact>
      <ContactID>8a777d01-8bfb-4623-807d-189d3f077e81</ContactID>
      <ContactStatus>ACTIVE</ContactStatus>
      <Name>John Doe</Name>
      <Addresses>
        <Address>
          <AddressType>POBOX</AddressType>
        </Address>
        <Address>
          <AddressType>STREET</AddressType>
        </Address>
      </Addresses>
      <Phones>
        <Phone>
          <PhoneType>DDI</PhoneType>
        </Phone>
        <Phone>
          <PhoneType>DEFAULT</PhoneType>
        </Phone>
        <Phone>
          <PhoneType>FAX</PhoneType>
        </Phone>
        <Phone>
          <PhoneType>MOBILE</PhoneType>
        </Phone>
      </Phones>
      <UpdatedDateUTC>2021-11-17T18:03:41.873</UpdatedDateUTC>
      <IsSupplier>false</IsSupplier>
      <IsCustomer>false</IsCustomer>
      <HasAttachments>false</HasAttachments>
    </Contact>
</Contacts>
</Response>

This is just one record - the response contains more than one. 

I tried to use Table.Transform columns in #"Parsed XML" and it returns the below; 

Would anyone know how can I transform the above XML to table format - example; 

ContactID	ContactNumber	Contactstatus	Name	FirstName	LastName
8a777d01-8bfb-4623-807d-189d3f077e81	99d248f11c2540af002806840bf3be3bfa95dd7f7afac17d3c	ACTIVE	John Doe	John	Doe
9b777d01-8bfb-4623-807d-189d3f077e81	10d248f11c2540af002806840bf3be3bfa95dd7f7afac17d3c	ACTIVE	Jane Doe	Jane	Doe

Thanks in advance! 

[v-jingzhang]

Hi @yanika_a 

Can you first try below query and let me know what result does this Source step return?

let
AccessToken = "Bearer xxxxxxxxxxxxxxxxxxxxxxxxxxxx",
Source = Web.Contents("https://api.xero.com/api.xro/2.0/Contacts", [Headers=[#"Content-Type"="application/json", #"xero-tenant-id"="xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", #"Accept"= "application/json", Authorization=AccessToken]])
in
Source

If it's possible to transform the response data into a table that each row has an XML record, then things will get easy. 

Use Xml.Tables to wrap the XML content. Then expand Contacts column. Detailed information in Contacts will be expanded to the same row. To get FirstName and LastName column, you can duplicate Name column first, then split the Name-Copy column by delimiter. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Gabriel_Walkman]

I'm not familiar with xml's, but what does that button in the top right corner of your last image do?