Return Value based on two columns conditions

Hi MisiakPol ,

Paste this into a new blank query using Advanced Editor to see the steps I took:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWOlWB0IxwiIA4oyk1PhIsZAHFiamFeSWVIJFzSB6TFCNsAIagCYY4wsY4wug2pqLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Invoice = _t, #"Invoice line" = _t, #"Error Message" = _t]),
    clearBlanks = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Error Message"}),
    chgTypes = Table.TransformColumnTypes(clearBlanks,{{"Invoice", Int64.Type}, {"Invoice line", Int64.Type}, {"Error Message", type text}}),
    groupInvoice = Table.Group(chgTypes, {"Invoice"}, {{"data", each _, type table [Invoice=nullable number, Invoice line=nullable number, Error Message=nullable text]}}),
    addReturn = Table.AddColumn(groupInvoice, "Return", each if List.IsEmpty(List.RemoveNulls([data][Error Message])) then "Not Blocked" else "Blocked"),
    expandDataCol = Table.ExpandTableColumn(addReturn, "data", {"Invoice line", "Error Message"}, {"Invoice line", "Error Message"})
in
    expandDataCol

SUMMARY:

1) groupInvoice = Group the table on [Invoice] and add an 'All Rows' aggregation column (called 'data').

2) addReturn = Evaluate the [Error Message] column in the nested [data] tables to see if the list is empty (i.e. no error messages). If it is, output "Not Blocked", otherwise "Blocked".

3) expandDataCol = Reinstate your grouped columns from the data table.

I get the following output:

( Greg_Deckler - Sorry to butt in, but this IS the Power Query forum 😉 )

Pete