Parse text as JSON or XML
In Power Query, you can parse the contents of a column with text strings by identifying the contents as either a JSON or XML text string.
You can perform this parse operation by selecting the Parse option found inside the following places in the Power Query editor:
Transform tab—This option transforms the existing column by parsing its contents.
Add column tab—This option adds a new column to the table parsing the contents of the selected column.
For this article, you're using a sample table that contains the following columns that you need to parse:
SalesPerson—Contains unparsed JSON text strings with information about the FirstName and LastName of the sales person, as in the following example.
{ "id" : 249319, "FirstName": "Lesa", "LastName": "Byrd" }
Country—Contains unparsed XML text strings with information about the Country and the Division that the account has been assigned to, as in the following example.
<root> <id>1</id> <Country>USA</Country> <Division>BI-3316</Division> </root>
The sample table also contains an Account column that contains the account number and an assigned on date.
The goal is to parse the above mentioned columns and expand the contents of those columns to get this output.
Select the SalesPerson column. Then select JSON from the Parse dropdown menu inside the Transform tab. These steps transform the SalesPerson column from having text strings to having Record values. You can select anywhere in the whitespace inside the cell of the Record value to get a detailed preview of the record contents at the bottom of the screen.
Select the expand icon next to the SalesPerson column header. From the expand columns menu, select only the FirstName and LastName fields. Also ensure that Use original column name as prefix is selected.
The result of that operation gives you the following table.
Select the Country column. Then select the XML button from the Parse dropdown menu inside the Transform tab. These steps transform the Country column from having text strings to having Table values. You can select anywhere in the whitespace inside the cell of the Table value to get a detailed preview of the contents of the table at the bottom of the screen.
Select the expand icon next to the Country column header. From the expand columns menu, select only the Country and Division fields. Also ensure that Use original column name as prefix is selected.
You can define all the new columns as text columns. The result of that operation gives you the output table that you're looking for.