Formatting XML API data connection

[kyle-intellese]

I am still new to all this (PowerBI, APIs, and XML) please forgive my ignorance. I am trying to use the Church Community Builder API. The example is: make a request to

https://yourchurch.ccbchurch.com/api.php?srv=individual_attendance&individual_id=48

And get back:

<?xml version="1.0" encoding="UTF-8"?>
<ccb_api>
  <request>
    <parameters>
      <argument value="individual_attendance" name="srv"/>
      <argument value="48" name="individual_id"/>
    </parameters>
  </request>
  <response>
    <service>individual_attendance</service>
    <service_action>execute</service_action>
    <availability>public</availability>
    <individuals count="1">
      <individual id="48">
        <name>Little Joe</name>
        <attendances>
          <attendance>
            <event id="776">Sunday School</event>
            <group id="1">Entire Church Group</group>
            <occurrence>2012-06-17 09:00:00</occurrence>
          </attendance>
          …
          <attendance>
            <event id="1806">Kids Club!</event>
            <group id="1">Entire Church Group</group>
            <occurrence>2013-10-17 09:00:00</occurrence>
          </attendance>
        </attendances>
      </individual>
    </individuals>
  </response>
</ccb_api>

But when I connect in PowerBI the result is:

Link to pic incase it doesn't dipaly above: https://preview.redd.it/xml-api-data-connection-v0-lon523uaqcyc1.png?width=583&format=png&auto=webp&...

When I request with Postman or curl I get the expected result. If I understand correctly, I need to tell PowerBI how to format the "individuals" table - but I don't understand how to do that.

Is anyone up for explaining what I need to do?

[v-xinruzhu-msft]

Hi @kyle-intellese 

Based on the code you have  offered, your xml file is from web,  you can consider to change the source from xml.table to web connector, you can refer to the following link.

https://learn.microsoft.com/en-us/power-query/connectors/xml#load-an-xml-file-from-the-web

Peace & Security Data Hub (un.org)

Best Regards!

Yolo Zhu

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

[kyle-intellese]

I've gotten most of it figured out with:

let
// Load XML data from the web
Source = Xml.Tables(Web.Contents("https://faithbiblechurch.ccbchurch.com/api.php?srv=individual_attendance")),

// Extract the main table
Table1 = Source{1}[Table],

// Expand the "individuals" column
ExpandIndividuals = Table.ExpandTableColumn(Table1, "individuals", {"individual"}, {"count"}),

// Expand the "individual" column
ExpandIndividual = Table.ExpandTableColumn(ExpandIndividuals, "count", {"name", "attendances", "id"}, {"individual.name", "individual.attendances", "individual.id"}),

// Expand the "Attendances" column
ExpandAttendances = Table.ExpandTableColumn(ExpandIndividual, "individual.attendances", {"attendance"}),

// Expand the "Attendance" column
ExpandIndividualAttendance = Table.ExpandTableColumn(ExpandAttendances, "attendance", {"event id", "group id", "occurrence"}, {"attendance.event id", "attendance.group id", "attendance.occurrence"}),

// Convert occurrence column to date data type
ConvertOccurrenceToDate = Table.TransformColumnTypes(ExpandIndividualAttendance, {{"attendance.occurrence", type datetime}})
in
ConvertOccurrenceToDate

Resulting in:

BUT there are three issues: The attendance.event id, attendance.group id, individual.id columns are null even though the XML does have data for them.

[kyle-intellese]

Ok, working through it step-by-step, I have:

let
// Load XML data from the web
Source = Xml.Tables(Web.Contents("https://faithbiblechurch.ccbchurch.com/api.php?srv=individual_attendance&individual_id=3321")),

// Extract the main table
Table1 = Source{1}[Table],

// Expand the "individuals" column
ExpandIndividuals = Table.ExpandTableColumn(Table1, "individuals", {"count"}, {"individuals.count"}),

// Convert "count" column to type int64
ConvertCount = Table.TransformColumnTypes(ExpandIndividuals,{{"individuals.count", Int64.Type}})

in
ConvertCount

which shows individuals.count as null. 

But if I go to my Navigation step, and click on the "Table" link in the "individuals" column

I can see that there is a value there.

[kyle-intellese]

I figured out one this i was doing wrong (where I was utting the code...).
I am now useing:

let
// Load XML data from the web
Source = Xml.Tables(Web.Contents("https://faithbiblechurch.ccbchurch.com/api.php?srv=individual_attendance&individual_id=3321")),

// Extract the main table (assuming Table1 is the main table)
Table1 = Source{1}[Table],

// Expand the "individuals" column
ExpandIndividuals = Table.ExpandTableColumn(Table1, "individuals", {"count"}, {"individuals.count"}),

// Expand the "individual" column
ExpandIndividual = Table.ExpandTableColumn(ExpandIndividuals, "individual", {"name", "id"}, {"individual.name", "individual.id"}),

// Expand the "attendances" column
ExpandAttendances = Table.ExpandTableColumn(ExpandIndividual, "attendances", {"event id", "group id", "occurrence"}, {"attendance.event id", "attendance.group id", "attendance.occurrence"}),

// Convert occurrence column to datetime
ConvertOccurrence = Table.TransformColumnTypes(ExpandAttendances, {{"attendance.occurrence", type datetime}})
in
ConvertOccurrence

And am gettign the error: Expression.Error: The column 'individual' of the table wasn't found.

[kyle-intellese]

I tried some PowerQuery, and it didn't work:
let

Source = Table1,

ExpandIndividuals = Table.ExpandTableColumn(Source, "individuals", {"count"}, {"individuals.count"}),

ExpandIndividual = Table.ExpandTableColumn(ExpandIndividuals, "individual", {"name", "id"}, {"individual.name", "individual.id"}),

ExpandAttendances = Table.ExpandTableColumn(ExpandIndividual, "attendances", {"event id", "group id", "occurrence"}, {"attendance.event id", "attendance.group id", "attendance.occurrence"}),

ConvertOccurrence = Table.TransformColumnTypes(ExpandAttendances, {{"attendance.occurrence", type datetime}})

in

ConvertOccurrence