You can parse (or deconstruct) the contents of a column with text strings that contain JSON or XML. To help illustrate how to use the Parse command, the following sample data starts with JSON and XML data entered as text fields.   

Sample JSON and XML data

For more information on importing JSON and XML, see Import data from external data sources.

JavaScript Object Notation (JSON) is a common data format, and you can import it into Excel.

To transform the SalesPerson column from text strings to a structured Record column:

  1. Select the SalesPerson column.

  2. Select  Transform > Parse > JSON.

  3. Select Record to see the values.

    Viewing the underlying structured Record

  4. Select the Expand Expand icon next to the SalesPerson column header. From the Expand columns dialog box, select only the FirstName and LastName fields.

    Expanding the Record into the table

Result

The results of expanding the record

Extensible Markup Language (XML) is a common markup and data format, and you can import it into Excel.

To transform the Country column from text strings to Table values.

  1. Select the Country column.

  2. Select Transform Parse > XML.

  3. Select Table to see the values.

    Viewing the underlying values in the structured Table

  4. Select the Expand  Expand icon next to the Country column header. From the Expand columns dialog box, select only the Country and Division fields.

    expanding the structured Table into the query table

Result

The results of expanding the Table

Tip     With a Table, you can also select Aggregate to aggregate data in different ways, such as Sum or Count. For more information, see Aggregate data from a column.

See Also

Power Query for Excel Help

Work with a List, Record, or Table structured column

Parse text as JSON or XML (docs.com)

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×