CODEWITHSUNDEEP
excelerror-handlingpowerquery
greg
greg

Reputation: 41

Power query Error.Record

How do I spit out records, to show up in the workbook queries pane as error records pls?

For example, if the [AcctClass] <> [_checkAcctClass] columns do not match, reject that record as an error

let 
    source = AccountIDsWithDuplicates,
    grouped = Table.Group(source, {"AcctID"}, {{"AcctClass", each List.Max([AcctClass]), type logical}, {"_checkAcctClass", each List.Min([AcctClass]), type logical}, {"Sum_Bal", each List.Sum([#"Bal_EUR"]), type number}}),
    // Make sure accounts only have one AcctClass 
    ErrorRecords = Table.SelectRows(grouped,  each([AcctClass] <> [_checkAcctClass])
in
    grouped

Upvotes: 0

Views: 341

Answers (1)

Carl Walsh
Carl Walsh

Reputation: 6949

Composing Table.TransformRows to create errors and then putting them back in a table with Table.FromRecords might do what you want?

= Table.FromRecords(Table.TransformRows(grouped, each if [AcctClass] <> [_checkAcctClass] then error "didn't match" else _), Value.Type(grouped))

If the first row is an error then Table.FromRecords will completely break, but you can work around that by telling it what table type to expect.

Example mashup:

let
    Source = Csv.Document("AcctID,AcctClass
        1,false
        1,true
        2,true
        2,true"),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    AccountIDsWithDuplicates = Table.TransformColumnTypes(#"Promoted Headers",{{"AcctID", Int64.Type}, {"AcctClass", type logical}}),
    grouped = Table.Group(AccountIDsWithDuplicates, {"AcctID"}, {{"AcctClass", each List.Max([AcctClass]), type logical}, {"_checkAcctClass", each List.Min([AcctClass]), type logical}}),
    ErrorRecords = Table.FromRecords(Table.TransformRows(grouped,  each if [AcctClass] <> [_checkAcctClass] then error "didn't match" else _), Value.Type(grouped))
in
    ErrorRecords

Upvotes: 1

Related Questions