Results 1 to 6 of 6

Thread: Making this XML data readable? VBA or powerquery

  1. #1
    Board Regular
    Join Date
    Nov 2016
    Posts
    573
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Making this XML data readable? VBA or powerquery

    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)

    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

    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

    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 by JumboCactuar; Apr 26th, 2019 at 07:21 PM.

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,329
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Making this XML data readable? VBA or powerquery

    post a link to shared xml file
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular
    Join Date
    Nov 2016
    Posts
    573
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making this XML data readable? VBA or powerquery

    Quote Originally Posted by sandy666 View Post
    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

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,329
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Making this XML data readable? VBA or powerquery

    here is

    Column1 Column2 __________ NAME LOCATION IDNO
    NAME John John UK
    171
    LOCATION UK Adam France
    189
    IDNO
    171
    Rachel UK
    177
    NAME Adam Ben Netherlands
    155
    LOCATION France Luke Spain
    144
    IDNO
    189
    Jack UK
    141
    NAME Rachel David Poland
    179
    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


    Code:
    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
    Last edited by sandy666; Apr 26th, 2019 at 08:34 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    Board Regular
    Join Date
    Nov 2016
    Posts
    573
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making this XML data readable? VBA or powerquery

    Thankyou I will try and work with this

    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 by JumboCactuar; Apr 26th, 2019 at 09:01 PM.

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,329
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Making this XML data readable? VBA or powerquery


    you can remove this line if you want

    from:
    Code:
        Promote = Table.PromoteHeaders(Trans, [PromoteAllScalars=true]),
        Type2 = Table.TransformColumnTypes(Promote,{{"NAME", type text}, {"LOCATION", type text}, {"IDNO", Int64.Type}})
    in
        Type2
    to:
    Code:
        Promote = Table.PromoteHeaders(Trans, [PromoteAllScalars=true])
    
    in
        Promote
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •