Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MisiakPol
Frequent Visitor

Return Value based on two columns conditions

Hi all!

I have a table that looks like this:

MisiakPol_0-1664867798788.png

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:

MisiakPol_1-1664867943385.png

 

Thanks in advance 🙂

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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:

BA_Pete_0-1664872991527.png

 

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

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

5 REPLIES 5
BA_Pete
Super User
Super User

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:

BA_Pete_0-1664872991527.png

 

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

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@MisiakPol Perhaps:

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors