Forum Discussion
Return Value based on two columns conditions
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 🙂
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 expandDataColSUMMARY:
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
5 Replies
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 expandDataColSUMMARY:
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
BA_Pete Yeah, I missed that, hazards of using the All Forums view.
MisiakPol Perhaps:
Return Column = VAR __Invoice = [Invoice] VAR __Table = FILTER('Table',[Invoice] = __Invoice && [Error Message] <> BLANK()) RETURN IF(__Table = BLANK(),"Not Blocked","Blocked")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!
