Example of forcing valid data on queries in Power BI

PingLog valid query else explicit error.pq

// PingLog - returns valid table otherwise entire query errors
// Output looks like: https://i.imgur.com/60oUF0C.png
let
    Source = Sql.Databases("sandbox"),
    nin = Source{[Name="nin"]}[Data],
    PingLog = nin{[Schema="dbo",Item="PingLog"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(
        PingLog,
        {
            {"Latency", "Ping"},
            {"DestIp", "Destination Ip"},
            {"Dest", "Destination"}
        }
    ),

    #"Reordered Columns" = Table.ReorderColumns(
        #"Renamed Columns",
        {
            "DatetimeUtc", "Status", "Ping",
            "Destination", "Destination Ip", "Source"
        }
    ),

    #"Sorted Rows" = Table.Sort(
        #"Reordered Columns",
        {{"DatetimeUtc", Order.Descending}}
    ),

    // if the distinct table test fails, the entire query fails
    // it isn't allowed to silently fail while appearing successful

    // this function is optional
    // prints a list values as a `csv` without having to drill down
    // ( see the attached screenshot )
    list_to_string = (text_list as list) as text =>
        let 
            seperator = ", ",
            template = "{ #{0} }",
            combined_string = Text.Combine(text_list, seperator)
        in
            Text.Format(template, {combined_string}),

    final_table = #"Sorted Rows", // this used to be my final step

    // declare the columns used for testing whether the table is distinct 
    // ie: the Table's primary keys
    distinct_columns = { "DatetimeUtc", "Destination", "Source" }, 

    final_table_otherwise_fail = if 
        Table.IsDistinct(
            final_table,
            distinct_columns
        )
    then
        final_table as table
    else 
        error [
            Reason = "ColumnsNotDistinct",
            Message = "Required distinct columns found duplicate records",
            Detail = "Failed PKs = " & list_to_string( distinct_columns )
        ]

in 
    final_table_otherwise_fail