Results 1 to 6 of 6

Thread: How to Expand All Levels Automatically when Importing XML files into Power Query

  1. #1
    Seeker seanhod's Avatar
    Join Date
    Feb 2020
    Posts
    7
    Articles
    0
    Excel Version
    365

    Question How to Expand All Levels Automatically when Importing XML files into Power Query



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I have been trying to find information on importing XML files into Power Query. I can't find anything reliable online. Does anybody have any experience with this and if so could they please recommend some resources on it.

    thanks,
    Seán

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,435
    Articles
    0
    Excel Version
    Office 365 Subscription
    Which version of Excel do you have? What is the issue exactly? Did you look at this?

    https://www.myexcelonline.com/blog/i...t-transform-2/

    Data > Get Data > From File > From XML - at what point is this not working for you?
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Seeker seanhod's Avatar
    Join Date
    Feb 2020
    Posts
    7
    Articles
    0
    Excel Version
    365
    Hi Ali,

    thanks for your reply. Apologies, my question was a bit vague. I am able to get the XML file imported but there are many levels in the XML file I am importing. Is there any way to automatically expand all the levels/tables or do I need to set this up manually the first time around?

    thanks,
    Seán

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,435
    Articles
    0
    Excel Version
    Office 365 Subscription
    You are right - your question was very vague! I do not have enough experience to help, but others will. I will add some detail to the title of your opening post.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,379
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi Seanhod,

    Do you by any chance have a sample file you can upload? While XML is structured, it would be helpful to see the structure you're looking at. Doesn't need to be real, just a representation of what you're looking at.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Seeker seanhod's Avatar
    Join Date
    Feb 2020
    Posts
    7
    Articles
    0
    Excel Version
    365
    Hi Ken,

    thanks so much for getting back to me and apologies for the delay on my side.

    It's not letting me upload the XML file - keeps saying invalid file. I have attached the file.

    I am actually trying to import data from multiple XML files to Power Query. All XML files are in the exact same format in the same folder.

    Is there something similar to
    Code:
    each Excel.Workbook([Content]
    for XML files.

    Thanks,
    Seán
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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