Making this XML data readable? VBA or powerquery

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
684
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:

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
684
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,456
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:

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
684
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:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,456
:) :) :)
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
 

Watch MrExcel Video

Forum statistics

Threads
1,100,060
Messages
5,472,213
Members
406,809
Latest member
haf19

This Week's Hot Topics

Top