Return Value based on two columns conditions

[MisiakPol]

Hi all!

I have a table that looks like this:

What I want to achieve is to return in a new column "Blocked" for the whole invoice if any of the lines contains an error message. So the result for this example table would be the following:

Thanks in advance 🙂

[BA_Pete · Accepted solution]

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

[MisiakPol]

Hi @BA_Pete ,

That's exactly the result I was going for. Thank you very much for taking your time to explain how it works! 🙂

[Greg_Deckler]

@BA_Pete Yeah, I missed that, hazards of using the All Forums view.

[Greg_Deckler]

@MisiakPol Perhaps:

Return Column = 
  VAR __Invoice = [Invoice]
  VAR __Table = FILTER('Table',[Invoice] = __Invoice && [Error Message] <> BLANK())
RETURN
  IF(__Table = BLANK(),"Not Blocked","Blocked")

[MisiakPol]

Hello @Greg_Deckler ,

As @BA_Pete wrote, I was looking for a Power Query solution, nevertheless also big thank you that you contributed and helped, appreciate it a lot!