Forum Discussion

ChrisAtMAF's avatar
ChrisAtMAF
Frequent Visitor
5 years ago

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...
Tutorial Requests
lbendlin's avatar
lbendlin
Super User
5 years ago

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's avatar
ChrisAtMAF
Frequent Visitor
5 years ago

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's avatar
    lbendlin
    Super User
    5 years ago

    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's avatar
      ChrisAtMAF
      Frequent Visitor
      5 years ago

      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?