JumboCactuar
Well-known Member
- Joined
- Nov 16, 2016
- Messages
- 785
- Office Version
- 365
- Platform
- Windows
Hi,
i get data from web which is an XML file with metadata and when loaded it is output like this:
(COLUMN1 / COLUMN 2 is actual header in A1/B1)
<tbody>
</tbody>
Now i need to manipulate it so its output like below:
<tbody>
</tbody>
I got a formula solution before from reddit though this formula assumes there is 3 columns to be output where i would like it to work with XML that require more than this.
example if the XML data was like:
COLUMN1
Name
Location
Age
IDNo
Gender
Name
Location
Age
IDNo
Gender
then it would output into 5 columns.
I thought this could probably be done directly in powerquery but cant see how
Appreciate any help
i get data from web which is an XML file with metadata and when loaded it is output like this:
(COLUMN1 / COLUMN 2 is actual header in A1/B1)
COLUMN1 | COLUMN2 |
NAME | John |
LOCATION | UK |
IDNO | 171 |
NAME | Adam |
LOCATION | France |
IDNO | 189 |
NAME | Rachel |
LOCATION | UK |
IDNO | 177 |
NAME | Ben |
LOCATION | Netherlands |
IDNO | 155 |
NAME | Luke |
LOCATION | Spain |
IDNO | 144 |
NAME | Jack |
LOCATION | UK |
IDNO | 141 |
NAME | David |
LOCATION | Poland |
IDNO | 179 |
<tbody>
</tbody>
Now i need to manipulate it so its output like below:
NAME | LOCATION | IDNO |
John | UK | 171 |
Adam | France | 189 |
Rachel | UK | 177 |
Ben | Netherlands | 155 |
Luke | Spain | 144 |
Jack | UK | 141 |
David | Poland | 179 |
<tbody>
</tbody>
I got a formula solution before from reddit though this formula assumes there is 3 columns to be output where i would like it to work with XML that require more than this.
Code:
=INDEX($B$2:$B$1000,COLUMN(A1)-1+ROW(A1)*3-2,1)
example if the XML data was like:
COLUMN1
Name
Location
Age
IDNo
Gender
Name
Location
Age
IDNo
Gender
then it would output into 5 columns.
I thought this could probably be done directly in powerquery but cant see how
Appreciate any help
Last edited: