- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

TRUE FALSE if group contains value
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?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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].
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Not my area of expertise but try:
TRUE/FALSE = CALCULATE(COUNTA(Query1[Order Nr]), Query1[Value]="B") > 0
Seems to give the desired results
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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"})

Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.

User | Count |
---|---|
14 | |
9 | |
8 | |
7 | |
7 |
User | Count |
---|---|
13 | |
11 | |
9 | |
6 | |
6 |