Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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! 🙂
@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!