Forum Discussion
Converting XML.Tables into usable expandable columns
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?