TRUE FALSE if group contains value

[joshua1990]

Hi experts!

I would like to get a TRUE/FALSE if a group contains a specific value.

The structure is like this:

Order Nr	Value	TRUE / FALSE
100	A	TRUE
100	B	TRUE
102	A	FALSE
102	C	FALSE

As you can see there a multiple records for each order.

I would like to get a TRUE/ FALSE if a group contains in column "Value" a 'B'.

How is this possible?

[AlexisOlson]

Another method is a self-merge and then check if each of the merged subtables has "B" in the values column.

Here's a sample M query you can paste into the Advanced Editor of a new Blank Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRclSK1YGxnaBsIyRxENtZKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Nr" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Nr", Int64.Type}, {"Value", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Order Nr"}, #"Changed Type", {"Order Nr"}, "TRUE / FALSE", JoinKind.LeftOuter),
    #"Check for B" = Table.TransformColumns(#"Merged Queries",{{"TRUE / FALSE", each List.Contains([Value], "B"), type logical}})
in
    #"Check for B"

[Vijay_A_Verma]

Below is an alternative solution. Put following in a custom column (Replace #"Changed Type" with your previous step)

= List.Contains(List.Zip({#"Changed Type"[Order Nr],#"Changed Type"[Value]}),{[Order Nr],"B"})

Marginal performance to the tune of 10-20% can be gained by buffering the lists if you have got thousands of records. These 2 lists are - #"Changed Type"[Order Nr] and #"Changed Type"[Value].

[ronrsnfld · Accepted solution]

Group by Order; then aggregate by the testing if the Value in each group contains "B":

let

//change next line to reflect your actual source of data
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Nr", Int64.Type}, {"Value", type text}}),


    #"Grouped Rows" = Table.Group(#"Changed Type", {"Order Nr"}, {
        {"Grouped", each _, type table [Value=nullable text]},
        {"TRUE/FALSE", each List.Contains([Value],"B"), type logical}
        }),
    
    #"Expanded Grouped" = Table.ExpandTableColumn(#"Grouped Rows", "Grouped", {"Value"}, {"Value"})
in
    #"Expanded Grouped"

[jdelmonico]

Is there any way to do this with a measure? The way my company has things set up I am not able to add columns or access the query itself. I have tried messing with groupby and summarize but I am stumped. Thank you!

[ronrsnfld]

Not my area of expertise but try:

TRUE/FALSE = CALCULATE(COUNTA(Query1[Order Nr]), Query1[Value]="B") > 0

Seems to give the desired results

[jdelmonico]

More accurately, I am trying to create a measure that sums up all of the Orders that contain Value "B" across several lines. I suppose I should start a new thread.

[joshua1990]

@ronrsnfld : Thanks a lot! It works perfectly well. What if the original table has 3 columns? How would you adjust the query to get the 3 columns after expand again?

[ronrsnfld]

There are several ways.

You could modify the "Grouped" and "Expanded Grouped" lines:

 #"Grouped Rows" = Table.Group(#"Changed Type", {"Order Nr"}, {
        {"Grouped", each _, type table [Value=nullable text, 3rd Column=nullable text]},
        {"TRUE/FALSE", each List.Contains([Value],"B"), type logical}
        }),
    
    #"Expanded Grouped" = Table.ExpandTableColumn(#"Grouped Rows", "Grouped", {"Value","3rd Column"}, {"Value","3rd Column"})