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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
joshua1990
Post Prodigy
Post Prodigy

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 NrValueTRUE / FALSE
100ATRUE
100BTRUE
102AFALSE
102CFALSE

 

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?

1 ACCEPTED SOLUTION
ronrsnfld
Super User
Super User

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"

ronrsnfld_0-1652623029495.png

 

 

 

View solution in original post

8 REPLIES 8
AlexisOlson
Super User
Super User

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
Super User
Super User

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
Super User
Super User

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"

ronrsnfld_0-1652623029495.png

 

 

 

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!

Not my area of expertise but try:

 

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

 

Seems to give the desired results

 

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.

@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?

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

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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

Top Solution Authors
Top Kudoed Authors