Clash XML Report Export & Power Query Issue

[cba]

I'm in the process of figuring out a way of eliminating a unwanted "copy/paste" process.

I used to copy paste the data from a HTML tabular report into my excel file that is calculating;

- a suitable clash name,

- assigning who is responsible.

- time scheduleing.

- 2D Plot of selected clashes

Example of 2D Plot

I'm hoping to use Power Query to eleminate the copy paste process.

I've exported the clashes that have been detetcted in navisworks manage.
I Prefer to use the XML - Format. This file format includes more usable data than a HTML report.
I've tried to export XML as individual files and as combined. Same issues for both.

The XML files combined with Power Query should eliminate the copy paste process, and simply replace the data "automaticly" when exporting new reports from naviswork.

However the XML Data doesent seem to be faulty.
Using Power Query Indeks and modulus functions i've been able to expand/reformat the XML Data into a table.

Column 1 = Testname

Column 2 =Status

Column 3 = Clash Name

Column 4 =Date Detected

Column 5 = Comments

Column 6 = Distance

Column 7 = Image file name & Location

Column 8 = X

Column 9 = Y

Column 10 = Z

Column 11 = Element 1 ID

Column 12 = Element 1 Layer/Level

Column 13 = Element 1 Type Comment

Column 14 = Element 1 Revit Category

Column 15 = Element 1 Revit Family

Column 16 = Element 1 Revit Type

Column 17 = Element 1 Revit Workset

Column 18 = Element 1 Revit File

Column 19 = Element 1 Revit GUID

Column 20 = Element 1 ID

Column 21 = Element 1 Layer/Level

Column 22 = Element 1 Type Comment

Column 23 = Element 1 Revit Category

Column 24 = Element 1 Revit Family

Column 25 = Element 1 Revit Type

Column 26 = Element 1 Revit Workset

Column 27 = Element 1 Revit File

Column 28 = Element 1 Revit GUID

This works perfectly for the first 30% of the clashes.
But at one point the data is beginning to be inserted into the wrong columns.

I suspect it is because of Power Query Modulus function VS inconsistency in number of "rows" when exporting XML.

Any suggestions as to how i can get the data from my XML reformated to a excel table correctly?

Shows unwanted GUID. Should have been Revit type

[dgorsman · Accepted solution]

Don't know about "Power Query" - sorry.  When I deal with XML data it's usually programmatically using either the MSXML DOM (ActiveX) or System.XML namespace (dotNET), using XPath to select nodes.

XML doesn't have "rows" like a table in Excel, which is one of it's benefits.  You don't need to have exactly the same data in every element; if the element doesn't apply it doesn't have to be present, or if other elements are applicable they can be added without affecting the others.

You should be searching by element name, content, and relative position and then iterating the results.  See https://www.w3schools.com/xml/xpath_intro.asp for syntax.

[cba]

Thanks for the inputs - I managed to find a way based on your "search for names" comment.

I was able to make it work using some conditional if formulas based on the element names.

Works like a charm.