Power query Error.Record

Question

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

Carl Walsh · Accepted Answer · 2015-07-02 08:44:44Z

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

Collectives™ on Stack Overflow

Power query Error.Record

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related