Issues Combining Xml files in a folder and loading to BI Model

[UK06B1]

Hello

I am struggling to combine c.a. 100 xml files from a folder  - when I link to the folder it comes up first with this

Then I select combine

This then creates the helper queries and i click on the expand colmn button which then gives me the individual Barcodes in the line item data ,  

But when i come to load this data into the model I get this error

I suspect its because there is one file in the folder where the record doesn’t follow the normal xml layout (and subsequently the record its referring too is the incorrect format?) ? But how do I find this ? I am looking to combine c.a. 100 xml files in a folder. Is there a way not to convert the xml to table or at least ignore or identify the files which have the errors in ?

Thanks for your help.

Neil

[ImkeF · Accepted solution]

Hi @UK06B1 ,
we can try another simple step first:
In the "Applied Steps"-window, check the step "Removed other columns1".
Then in the UI under "Add Column" add a column with this formula
Table.RowCount([Transform file (3)]
Then in the next step where the errors are removed, change the column reference "Transform file (3)" to the name of this new column.
This should force the table expression to be evaluated and then hopefully the error will actually surface.

[UK06B1]

Hello Imke, thanks for your help earlier in the week, but it seems i still cannot get this data to load in the way i would like, 

When I connect to the folder where the Xml files are stored then it creates the following link

I click Combine and transform , there are xml files which have errors in them and will not load (your solution earlier in the week helped me identify these). That said first I remove these files. NOTE the helper queries have the following in the advanced Editor (the first xml file in the folder works and the out put is as follows.  

Because there are some files with fundamental errors in them as said i filter them out first ( this is illustrated in the column from the helper query

Afte filtering out the uery then follows on as normal, but when I expand the table contents it then gives the following,

From this when I click on the first Table row it says the table is “empty”, but when I click on the “Table” in the second row the barcode is there. In the original Xml file this is because the first row is in fact empty for the barcode but populated for the 2nd row.

What I really need is for the resulting query to return the Barcode where there is one , and then return blank or Null (or even a zero) for the rows which don’t. You see in the screen shot that for the file 23128 where all rows of the Xml had a Barcode then it returns the barcode for all rows.

In the end I want to count the amount of records and essentially see which ones did not have a barcode. I would also like to have a table in BI with all line items that did not have the barcode.  See below what comes through at the moment – the files where the are records that don’t have a barcode is saying table – what I would like as an example for file 2312 is to have the rows with the barcodes and where there is a no barcode for it to say blank (And count the numbers of records that are blank).

Below is the full advanced editor I have at the moment.

Really appreciate your help with this.

Regards

Neil

[UK06B1]

Hi Imke, I wasnt convinced i had all the code in the correct order so I started fresh and did as you suggested - i now have what i think is complete data  - it has returned either A) the total row numbers for the tables which i think are correct - the errors I assume is where i have issues with the files. 

Once i get rigd of these the data loads 🙂 So all i think i need to do now is identify the reaosn for the erros (or if its big enough to be an issue with the data set i have ). 

Thanks loads for your help. 

Neil 

[UK06B1]

Hi Imke, 

So that seems to suggest that all the rows are errors ? I added the 2 additioal steps, I also include the full 

When i remove errors the table is blank , 

Does this seem to suggest that the rows in the Tables are all errors ? it seems strange ? 

Thanks 
Neil 

[ImkeF]

No worries @UK06B1 ,

please try the following:

[UK06B1]

Thanks for your continued patience Imke 🙂 

That now works, but when i now load the data to the model i get the same original issue - 

what i suspect the issue is, is that some columns when combining the files have nested tables in the columns - when i click combine and transform and select the first file i get the following but if i choose another random file i get tables appearing in the water capacity column as well. 

Do i first of all need to convert the columns into rows of data before proceeding to combine the files ? i suspect where the issue lies is that it says in one of the files there is "null" (or other text/digit) potentially but it cant then convert it to a Table when combining? and subsequently cant combine the data rows ? 

Hope i made the issue clear ? and thanks again for your support. 

Regards

Neil 

[ImkeF]

Hi @UK06B1 ,
you can add an additional step before expanding the columns that removes errors on the columnn to expand.

[UK06B1]

Hi Imke , thanks for your quick response , its first time i have linked to xml today and so a little bit of a novice on this (i am also no expert with detailed M Code) - any chance you could point me to what and how i would add what you suggest, below is the current view in the advanced editor - any help would be appreciated. 

Thanks Neil

[ImkeF]

Sure @UK06B1 ,
after the step "Removed other columns 1" you add the step where you remove errors based on column "Transform File 3".

[UK06B1]

Thanks Imke, i tried this , and suspect i made a basic error ? any ideas what i am missing  ? I added this as you sugegsted , 

but get this now ? thanks again 🙂