How to create a reuseable function to track Power Query errors

   Check list of all posts

As known, Power Query is an excellent data prep tool. It can be used as ETL to prepare data marts with dimensions and facts.  Fact table loading could become very important to keep data quality. This document is intended to provide a reusable function to retrieve all error records with detailed information.  most of the idea is from Learn How to Track Errors with a Power Query Error Table .

The overall transformations are documented as diagram below.

The script below is a fully workable Power Query script, which can plug into the Power Query editor. We can even post these exceptions on the visuals.

let

    Source = (InputQuery as table,QueryName as text, KeyColumn as text) => let

        Source = InputQuery,

        #"Added Query Name"= Table.AddColumn(Source, "Query", each QueryName),

        #"Kept Records Errors" = Table.SelectRowsWithErrors(#"Added Query Name",Table.ColumnNames(Source)),

        #"Marked Key Column" = Table.RenameColumns( #"Kept Records Errors",{{KeyColumn, "KeyColumn"}}),

        #"Transformed Row as Record" = Table.AddColumn(#"Marked Key Column", "CurrentRow", each _),

        #"Transformed Row Record as Table" = Table.AddColumn(#"Transformed Row as Record", "RECORD TO TABLE", each Record.ToTable([CurrentRow])),

        #"Expanded Table" = Table.ExpandTableColumn(#"Transformed Row Record as Table", "RECORD TO TABLE", {"Name", "Value"}, {"Name", "Value"}),

        #"Kept Columns with Error" = Table.SelectRowsWithErrors(#"Expanded Table", {"Value"}),

        #"Added Check Errors" = Table.AddColumn(#"Kept Columns with Error", "Check Errors", each try [Value]),

        #"Expanded Check Errors" = Table.ExpandRecordColumn(#"Added Check Errors", "Check Errors", {"Error"}, {"Error"}),

        #"Expanded Error" = Table.ExpandRecordColumn(#"Expanded Check Errors", "Error", {"Reason", "Message", "Detail"}, {"Error.Reason", "Error.Message", "Error.Detail"}),

        #"Removed Other Columns" = Table.SelectColumns(#"Expanded Error",{"KeyColumn", "Query", "Name", "Error.Reason", "Error.Message", "Error.Detail"})

    in

        #"Removed Other Columns"

in

    Source