Try before you error

Problem to solve: importing many files into Power BI where some are not complete and trigger an ODBC error.

While importing thousands of JSON files I bumped into the situation where some of the files weren’t following the structure and thus caused an error and the import process to stop. The files were supposed to have some general and customer information about an order and order lines structured under “lines”.

The images show the ODBC error (only the last line of the message is relevant) and a well-formed JSON file.

I used my newly discovered Powershell command ‘findstr’ to check if there were really JSON files without order lines (do I understand the error message correctly) and yes, many.

Finding a solution

The first answer to the question “how to implement error handling” brought me to this solution: https://community.powerbi.com/t5/Desktop/Error-handling-in-loading-files-from-folder/m-p/276960

If you click on the link you will probably think like me “that’s a lot of work”.

The second solution I bumped into was “use try-otherwise”. I added try and otherwise (all the bold text in the code example) to the M code, it worked. It changes the import mood from “must fit into structure” to “if it fits, just add the row, otherwise try to add as much data as possible as a new row”. It speaks for itself this solution fixes the import error but needs more data cleaning since most of the fact data are missing.

= (Parameter2) => let
        Source = Json.Document(Parameter2),
        #"Converted to Table" = Table.FromRecords({Source}),
        #"Changed Type" = try Table.TransformColumnTypes(#"Converted to Table",{{"id", Int64.Type}, {"orderDate", type datetime}, {"status", type text}, {"customer", type any}, {"lines", type any}}) otherwise #"Converted to Table"
    in
        #"Changed Type"

According to Yoast I should add more text. End of message.