Excel Power Query Errors

Question

In an Excel Power Query, I am trying to add a column (Table.AddColumn) to a query where the value for each row checks the entire row to see if any fields have an "Error" record but I'm at a loss at how I could do this. I know how to check individual fields using "try ... otherwise" but I'm not sure how to input the entire row. Basically my goal is to have a field indicating if ANY other field in a record (i.e., a row) has an error without hard keying every column into a function that checks each for an error.

Any help is appreciated!

Answer 1

I'm sure you could do some trickiness with joining back to Table.SelectRowsWithErrors.

Unfortunately I don't have time to figure out exactly what that would look like right now, but hopefully I can come back and update this at a later date, or someone else will figure it out and provide a better answer.

Answer 2

Adding to Jeff's approach . If you need all the rows with some indicator column like "errored" "Fine"

Create a function which returns the table before your last step , and reference it in two separate queries. In one query keep only errors and add custom column "Errored" In second query remove errors and add custom column "Fine"

Append above 2 queries to get your final result.

If you just needed the errored records , you can modify the original query and just keep errored records.

Answer 3

If you strictly want to have such a column, you can do following steps:

let
    Source = Table.ExpandListColumn(Table.FromRecords({[Name= {"Bob", "Jim", "Paul"}, Discount = .15] }), "Name"),
    AddedErrClmn = Table.AddColumn(Source, "ErrClmn", each if [Name] = "Jim" then [Name]-[Discount] else [Discount]),
    AddedIndexClmn = Table.AddIndexColumn(AddedErrClmn, "Index", 1, 1),
    ErrorlessIndexes = Table.RemoveRowsWithErrors(AddedIndexClmn)[Index],
    AddedCheckClmn = Table.AddColumn(AddedIndexClmn, "Validation", each if List.Contains(ErrorlessIndexes, [Index]) then "ok" else "Error")
in
   AddedCheckClmn

So you add index, clean table of errors and save resulting indexes in a list ErrorlessIndexes. Then add column with formula: search that list for each row's index.If found, row is Ok. If not, it contains errors.

Answer 4

Consider this

let
    Source = #table({"a","b","c"},{{1,2,3},{2,[3],4},{3,4,5}}),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    Nested = Table.AddColumn(#"Added Index", "Has Error", each Table.RowCount(Table.SelectRowsWithErrors(Record.ToTable(#"Added Index"{[Index]})))>0)
in
    Nested