Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ChrisAtMAF
Frequent Visitor

Converting XML.Tables into usable expandable columns

Hey,

 

I've got some XML with computer data inside it created along similar lines to this article: Creating Custom XML from .NET and PowerShell 

 

Hardware: https://pastebin.com/ca1rxYuu

Network: https://pastebin.com/dLwDPM33

 

I'm trying to import this data into Power Query in such a way that each Property valye is contained inside a suitable Expandable Table e.g.

 

ComputerSystem

-> ComputerSystem.Manufacturer

-> ComputerSystem.Model

 

NetworkAdapterConfiguration

-> NetworkAdapterConfiguration.Description

-> NetworkAdapterConfiguration.IPAddress (contains both IP addresses somehow)

 

However I can't find a way to do this in a simple way - so far I've got a mess which involves selecting column names, transposing the table, promoting headers, then expanding the columns, then doing the same on each layer of the XML file, and it feels like it should be possible to do it in a couple of lines recursively.

 

Am I missing anything? Or does Xml.Tables not deal with this style of XML particularly well?

 

Any assistance appreciated 🙂

5 REPLIES 5
Syndicate_Admin
Administrator
Administrator

Hey,

I need a solution for a similar problem.

If you can achieve it, could you please help me as well? 

lbendlin
Super User
Super User

Something like this?

let
    Source = Xml.Tables(File.Contents("C:\Users\xxx\Downloads\computer.xml")),
    Object = Source{0}[Object],
    #"Expanded Property" = Table.ExpandTableColumn(Object, "Property", {"Element:Text", "Attribute:Name"}, {"Element:Text", "Attribute:Name.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Property", "Attribute", each [#"Attribute:Name"] & ":" & [#"Attribute:Name.1"]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Attribute", "Element:Text", "Attribute:Name.1", "Attribute:Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Element:Text", "Value"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Attribute", "Value"})
in
    #"Removed Other Columns"

Hey @lbendlin ,

 

Thanks so much. Sorry, I should have been clearer what I've got.

 

I've got one of these XML documents for a number of computers in a Power Query column.

 

Your query looks great but I need the attribute names as columns, not rows, and with lots of them.

 

In other words, instead of:

 

ChrisAtMAF_3-1629287245734.png

 

 

I need something more like this:

 

ChrisAtMAF_0-1629287114707.png

 

Happy to provide my existing (messy) code if that would help explain better.

 

Kind regards,

Chris

I don't think you really want that.  Remember the fundamental difference between a bog standard table (nice and easy, two dimensions, fixed column structure) and XML or JSON which exist exactly because their ragged hierarchies do not fit into the rigid table structure.

 

Whenever someone asks you for variable number of columns - run away, and fast. Pivoting is the opposite of what Power Query is about . If you notice there are Unpivot commands, but no Pivot commands.  The pivoting should be done only in the Power BI UI, in the visuals.

Hey, I'm not after a variable number of columns per se - I have a single XML document for computers within Active Directory each of which contains data about that computer. The Object Name and Property Name attributes for each computer are static. I need Power Query to parse each XML document for each computer and extract that data in a usable format. For those Properties where there is more than one Value (e.g. IP Address) it just needs concatenating with e.g. commas to delimit.

 

Computer Name | ComputerSystem/Manufacturer | ComputerSystem/Model | ComputerSystem/SystemType | ComputerSystem/TotalPhysicalMemory(GB) | ComputerSystemProduct/Version

 

I understand the complexity around a variable number of columns - but this is the best way I have to squeeze lots of data into a limited amount of space in Active Directory - and the XML tables contain a predetermined set of column headings only - the Object Names of every document is set by me to be identical, they just contain different data. (Nobody's 'asking' me for this - this is me wanting to do this!)

I've got it working, but I'm convinced the way I'm doing it is too complicated; I'm asking if anyone thinks they would be able to simplify it?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.