Ravindra BagaleCourses & study guides मराठी Track your progress

5. Web Data, APIs and Google Sheets

5.8 XML Files

By Last updated:

Start with a problem

A partner sends dark stores as an XML feed. Ruhi opens it and sees tables inside tables.

Explain simply

Get data › XML uses Xml.Tables. Nested elements arrive as Table cells; expand only the fields you need and set types.

Show the data

web_stores.xml (5 store elements):

id city name
S01 Pune Kothrud
S02 Pune Baner
S03 Nashik Gangapur
S04 Nashik College Road
S05 Kolhapur Rajarampuri

Fictional sample data. Practice file: web_stores.xml, visuals_orders_apr_may_2026.csv (source: src/practice/).

Demonstrate

  1. Get data › More… › File › XML (or Web with the course-site link) › web_stores.xml.

    Your result should look like this:

    Navigator
    stores › store (Table)

  2. Select store › Transform Data.

    Your result should look like this:

    Columns
    id, city, name (5 rows)

  3. If the feed nests deeper, expand only id, city, name with the expand icon and untick the prefix.

  4. Set all three columns to Text; rename name to Store.

Show the result

Your result should look like this (fictional sample):

city Stores
Pune 2
Nashik 2
Kolhapur 1
Total 5

Explain what happened

Xml.Tables turned each repeated <store> element into a row and each child element into a column.

Explore further

  • Element names depend on how the XML was written; expand what you see in the preview.

Detailed reference for this lesson:

After JSON comes XML. Both hold nested data. In Power Query, an XML file opens as tables inside tables.

Get data › XML (under More, if it is not on the main list) or Get data › More… › File › XML. Power Query uses Xml.Tables behind the click. Nested elements arrive as Table cells. Click the expand icon on those columns and pick the child fields you need. Set types after the expand, not before, because a nested cell is not a number yet.

let
    Source = Xml.Tables(File.Contents("C:\Data\stores.xml")),
    Expanded = Table.ExpandTableColumn(Source, "store", {"id", "city", "name"})
in
    Expanded

The element names in your file will not be the word store unless the XML was written that way. Expand what you see in the preview, then rename.

Worked example. A fictional feed lists dark stores:

<stores>
  <store><id>S01</id><city>Pune</city><name>Kothrud</name></store>
  <store><id>S02</id><city>Nagpur</city><name>Dharampeth</name></store>
</stores>

After expand you have two rows, Pune and Nagpur. city is still text. Set the type to Text, and do not load the XML file path as a column into the report. The feed is practice data, not a live company API.

Ravindra Bagale's Tip

Many students expand every nested column "just in case" and the query becomes a dozen technical fields. Expand only id, city and name, then remove the rest. XML loves extra wrappers; your model should not. Don't make this mistake!

Fix common mistakes

Wrong result Reason Correction
A dozen technical columns Expanded every wrapper Expand only id, city, name
Store IDs lose leading zeros Auto type set to number Set Text
File path column loaded into the report Kept the source column Remove it

Practise independently

Practice task

Merge the XML stores with the practice orders on store name. Which store has the most orders?

Hints
  • Kothrud: 301, 306; College Road: 304, 307; Rajarampuri: 305, 308.
Solution

Three stores tie on 2 orders: Kothrud, College Road and Rajarampuri. Gangapur and Baner have 1 each.

Think like a professional

Agree with the partner on a schema (element names and types); then a feed change breaks loudly, not silently.

Thodkyaat sangaycha tar (quick recap)

Keep the base URL fixed, put the changing part in RelativePath, expand only the JSON fields you need, and refresh only as often as needed. Respect the site's rules. Now let us step into the world of Power Query!

Ravi Sir's Cards for this lesson

XML files in Power BI. Some older systems export data only as XML. You need to turn it into a table.Revision card

मराठी cards