Transform XML data in excel

Question

I have an xml file that when I import in to excel presents the data as such

Field	name
1	A
2	B
3	C
4	A
5	B
6	C
7	A
8	B
9	C

How can I transform this to

A	B	C
1	2	3
4	5	6
7	8	9

Answer 1

Hello KH_69,

Welcome to the Microsoft Community Support!

In order to transform XML data in excel you can use Power Query in Excel to transform your imported XML data into the desired format. Here's how you can do it:

1. Open Excel and go to Data → Get Data → From File → From XML.
2. Select your XML file and import it.
3. Once the data is loaded in a table format, click on Transform Data to open Power Query.
4. Select the "Field" column.
5. Click Transform → Pivot Column.
6. In the Values Column dropdown, choose the "Name" field.
7. Click OK—this will restructure your data.
8. If needed, rearrange columns by dragging.
9. Check that the values align under each appropriate column.
10. Click Close & Load, and your transformed data will appear in Excel.

Alternatively, if you'd prefer a manual formula-based approach, you can use Excel's INDEX() and MATCH() functions to extract the data dynamically into a structured table.

Best Regards, Van Johnson | Microsoft Community Support Specialist

Answer 2

Hi,

In cell D1 apply this dynamic formula:

=TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",, BYROW(GROUPBY(TRIMRANGE(B2:B15), TRIMRANGE(A2:A15), ARRAYTOTEXT,, 0), LAMBDA(a, ARRAYTOTEXT(a)))), ", ", ";"))

In order for the formula to work, you must use Office 365.

Hope this helps.

Answer 3

Hi, thanks for the reply.

The problem I'm having is that no data is being retrieved using the Get Data method

Answer 4

Hi,

This M code in Power Query works

let

Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], 

#"Grouped Rows" = Table.Group(Source, {"name"}, {{"Count", each Table.AddIndexColumn(\_,"Index",1)}}), 

#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Field", "Index"}, {"Field", "Index"}), 

#"Pivoted Column" = Table.Pivot(#"Expanded Count", List.Distinct(#"Expanded Count"[name]), "name", "Field"), 

#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}) 

in

#"Removed Columns"

Hope this helps.

Answer 5

Hello KH_69,

Welcome to the Microsoft Support Community.

It looks like you're trying to transform XML data in Excel to rearrange it into a specific format. Here's how you can do it:

This issue is not related to your Microsoft account or account registry. It's about formatting and transforming data in Excel after importing an XML file.

1. Import the XML File:
   • Open Excel and go to Data > Get Data > From File > From XML.
   • Select your XML file and click Import.
2. Rearrange the Data:
   • Once the data is imported, use Excel's Transpose feature:
     • Select the data you want to rearrange.
     • Copy it (Ctrl+C).
     • Right-click on an empty cell where you want the new format.
     • Choose Paste Special > Transpose.
3. Sort the Data:
   • If needed, use the Sort feature under the Data tab to organize the rows and columns.
4. Adjust Formatting:
   • Manually adjust the rows and columns to match your desired format.

• The following is a link to SFC: -Import XML data - Microsoft Support

Let me know if you need further clarification!

Best Regards,

Steve_rojers I Microsoft Community Support Specialist