Converting XML.Tables into usable expandable columns

[ChrisAtMAF]

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 🙂

[Syndicate_Admin]

Hey,

I need a solution for a similar problem.

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

[lbendlin]

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"

[ChrisAtMAF]

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:

I need something more like this:

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

Kind regards,

Chris

[lbendlin]

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.

[ChrisAtMAF]

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?