errorReport M function | Creates a record with overall error count and detailed error reporting table.

errorReport.pq

let
    fxErrorReport = (myTable as table, optional keyCols as list, optional scanCols as list ) as record =>
    let
        colNames = Table.ColumnNames( myTable ),
        validKeys = List.Intersect( { colNames, keyCols ?? {} } ),
        hasKeys = List.Count( validKeys ) >0,
        selCols = List.Intersect( { colNames, scanCols ?? {} } ),
        nTable = Table.SelectColumns( myTable, if List.Count( selCols ) >0 then validKeys & selCols else colNames ),
        reservedNames = { "Name", "Errors", "Reason", "Message", "Detail", "rowIndex", "nCol" },
        nameViolations = List.Intersect( { colNames, reservedNames } ),
        runLogic = if List.IsEmpty( nameViolations )
            then let
                t = if hasKeys = true 
                    then Table.SelectRowsWithErrors( nTable ) 
                    else Table.SelectRowsWithErrors( Table.AddIndexColumn( nTable, "rowIndex", 0, 1, Int64.Type)),
                getErrors = Table.SelectColumns( Table.AddColumn( t, "nCol", each 
                    Table.AddColumn( Table.SelectRowsWithErrors( Record.ToTable(_), {"Value"}), "Errors", 
                        each try [Value] catch (e)=> e )[[Name], [Errors]] ), 
                        if hasKeys then validKeys & {"nCol"} else {"rowIndex", "nCol"} ),
                errorTable = Table.ExpandRecordColumn( 
                    Table.ExpandTableColumn( getErrors, "nCol", {"Name", "Errors"} ),
                    "Errors", {"Reason", "Message", "Detail"} ) 
            in errorTable
            else error 
                "Please rename these columns in your table first: " & Text.Combine( nameViolations, ", " )
    in
        [
            Error count = Table.RowCount( runLogic ),
            Data = runLogic,
            Invalid key columns = List.Difference( keyCols ?? {}, validKeys ),
            Invalid scan columns = List.Difference( scanCols ?? {}, selCols )
        ],
        fxDocumentation = [
            Documentation.Name = " Create an Error Report ",
            Documentation.Description = " Returns a record containing 4 fields.
            'Error Count' shows the number of errors found in the input table. 
            'Data' contains the error reporting table. 
            'Invalid key columns' returns a list containing specified invalid key columns if any. 
            'Invalid scan columns' returns a list containing specified invalid scan columns if any. ",
            Documentation.Author = " Melissa de Korte ",
            Documentation.Version = " 1.0 "
    ]
in
    Value.ReplaceType( fxErrorReport, Value.ReplaceMetadata(
            Value.Type( fxErrorReport ),
            fxDocumentation
        )
    )