Making this XML data readable? VBA or powerquery

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
785
Office Version
  1. 365
Platform
  1. 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)

COLUMN1COLUMN2
NAMEJohn
LOCATIONUK
IDNO171
NAMEAdam
LOCATIONFrance
IDNO189
NAMERachel
LOCATIONUK
IDNO177
NAMEBen
LOCATIONNetherlands
IDNO155
NAMELuke
LOCATIONSpain
IDNO144
NAMEJack
LOCATIONUK
IDNO141
NAMEDavid
LOCATIONPoland
IDNO179

<tbody>
</tbody>

Now i need to manipulate it so its output like below:

NAMELOCATIONIDNO
JohnUK171
AdamFrance189
RachelUK177
BenNetherlands155
LukeSpain144
JackUK141
DavidPoland179

<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:

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
post a link to shared xml file

i cant as its confidential data, ive tried to find a similar example but no luck

basically when i load it in to powerquery it shows as multiple tables which i can expand to get the data like in the first table i posted.
Just need to know how to transform it further
 
Upvote 0
here is

Column1Column2__________NAMELOCATIONIDNO
NAMEJohnJohnUK
171​
LOCATIONUKAdamFrance
189​
IDNO
171​
RachelUK
177​
NAMEAdamBenNetherlands
155​
LOCATIONFranceLukeSpain
144​
IDNO
189​
JackUK
141​
NAMERachelDavidPoland
179​
LOCATIONUK
IDNO
177​
NAMEBen
LOCATIONNetherlands
IDNO
155​
NAMELuke
LOCATIONSpain
IDNO
144​
NAMEJack
LOCATIONUK
IDNO
141​
NAMEDavid
LOCATIONPoland
IDNO
179​

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
    Group = Table.Group(Type, {"Column1"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom", each Table.Column([Count],"Column2")),
    Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    RC = Table.RemoveColumns(Extract,{"Count"}),
    Split = Table.SplitColumn(RC, "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7"}),
    Trans = Table.Transpose(Split),
    Promote = Table.PromoteHeaders(Trans, [PromoteAllScalars=true]),
    Type2 = Table.TransformColumnTypes(Promote,{{"NAME", type text}, {"LOCATION", type text}, {"IDNO", Int64.Type}})
in
    Type2[/SIZE]
 
Last edited:
Upvote 0
Thankyou I will try and work with this

Rich (BB code):
Type2 = Table.TransformColumnTypes(Promote,{{"NAME", type text}, {"LOCATION", type text}, {"IDNO", Int64.Type}}


This line, does it mean will only work if NAME/LOCATION/IDNO exist? Like if the XML has different columns it won't work?[/code]


 
Last edited:
Upvote 0
:) :) :)
you can remove this line if you want :)

from:
Code:
    Promote = Table.PromoteHeaders(Trans, [PromoteAllScalars=true]),
    [COLOR="#FF0000"]Type2 = Table.TransformColumnTypes(Promote,{{"NAME", type text}, {"LOCATION", type text}, {"IDNO", Int64.Type}})[/COLOR]
in
    Type2

to:
Code:
    Promote = Table.PromoteHeaders(Trans, [PromoteAllScalars=true])

in
    Promote
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top