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"

     

     

     

ronrsnfld's avatar
ronrsnfld
Icon for Super User rankSuper User
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"

 

 

 

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

    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's avatar
      ronrsnfld
      Icon for Super User rankSuper User
      4 years ago

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

       

       

       

  • jdelmonico's avatar
    jdelmonico
    Frequent Visitor
    3 years ago

    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!

    • jdelmonico's avatar
      jdelmonico
      Frequent Visitor
      3 years ago

      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's avatar
      ronrsnfld
      Icon for Super User rankSuper User
      3 years ago

      Not my area of expertise but try:

       

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

       

      Seems to give the desired results