Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

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 ...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    4 years ago

    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"

     

     

     

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

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"