Forum Discussion

wsMac78AZ's avatar
wsMac78AZ
Frequent Visitor
4 years ago
Solved

Filter Rows Identical Content Across Columns

Hope someone is able to help me.

 

I have a table below. I want to identify rows that have identical text across the 4 columns.

 

 BreakfastLunchDinnerSupper
MondayCerealSteakSoupBiscuit
TuesdaySoupSoupSoupSoup
WednesdayCerealSpaghettiSpaghettiSoup
ThursdaySoupSteakCurryCake
FridayCakeCakeCakeCake
SaturdayPorridgePorridgeSoupSpaghetti
SundaySoupSpaghettiSteakSoup

 

I would like to display the result in a table as below:

 

TuesdaySoupSoupSoupSoup
FridayCakeCakeCake

Cake

 

Also, I hope to print it into the card as below:

 

2
Same food

 

Would appreciate any help in this regard. Thanks!

 

 

Need Help
  • johncolley's avatar
    johncolley
    4 years ago

     

    Hi wsMac78AZ ,

     

    You can create a calculated column such as:

    Same Food = IF( 
    Meals[Breakfast] = Meals[Lunch] && Meals[Breakfast] = Meals[Dinner] && Meals[Breakfast] = Meals[Supper], 1, 0)
    You can insert the column as a basic filter as per picture to limit the table and put the column on a card.

     

6 Replies

  • johncolley's avatar
    johncolley
    Solution Sage
    4 years ago

     

    Hi wsMac78AZ ,

     

    You can create a calculated column such as:

    Same Food = IF( 
    Meals[Breakfast] = Meals[Lunch] && Meals[Breakfast] = Meals[Dinner] && Meals[Breakfast] = Meals[Supper], 1, 0)
    You can insert the column as a basic filter as per picture to limit the table and put the column on a card.

     

    • danextian's avatar
      danextian
      Super User
      4 years ago

      Well, this is a much simpler solution 🙂

    • wsMac78AZ's avatar
      wsMac78AZ
      Frequent Visitor
      4 years ago

      Many thanks, John! Your approach is very simple and neat; I like it.

  • danextian's avatar
    danextian
    Super User
    4 years ago

    Hi wsMac78AZ ,

     

    There are many ways to do this but most of these approaches involve Power Query.

     

    Approach1 - Power Query only using the sample M Script below

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDBCoMwEET/Zc9+RQVvhYKCh5DDUhcNlkTW7KF/3yQmVsXTDMmbyRCl4OnsgF+ooCYm/ATTesI5qpMlyMOsbzEedKWgE1o3Ol/eSOR6Gmwh/70LjhN5b66+pLpJ+FKfp9TCnLpwpoQ2bHJ7PLmViLXohTfw5ThkRjrb8s4+J6XEnlccxx4/R+sf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Day = _t, Breakfast = _t, Lunch = _t, Dinner = _t, Supper = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", type text}, {"Breakfast", type text}, {"Lunch", type text}, {"Dinner", type text}, {"Supper", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Day"}, {{"Grouped", each _, type table [Day=nullable text, Breakfast=nullable text, Lunch=nullable text, Dinner=nullable text, Supper=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Unpivoted", each Table.UnpivotOtherColumns([Grouped], {"Day"}, "Attribute", "Value"), type table),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Grouped2", each Table.Group([Unpivoted], {"Day", "Value"}, {{"Count", each Table.RowCount(_), Int64.Type}}), type table),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Grouped2_Filtered", each Table.SelectRows([Grouped2], each [Count] = 4), type table),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Unpivoted", "Grouped2"}),
    #"Expanded Grouped" = Table.ExpandTableColumn(#"Removed Columns", "Grouped", {"Breakfast", "Lunch", "Dinner", "Supper"}, {"Breakfast", "Lunch", "Dinner", "Supper"}),
    #"Expanded Grouped2_Filtered" = Table.ExpandTableColumn(#"Expanded Grouped", "Grouped2_Filtered", {"Count"}, {"Count"}),
    #"Added Custom3" = Table.AddColumn(#"Expanded Grouped2_Filtered", "All True", each if [Count] = 4 then true else false, type logical),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom3",{"Count"})
in
    #"Removed Columns1"

     Result  - you can use All True column to filter only those with identical texts.

     

    Approach2 - Keep the original table, make a duplicate and unpivot and grouped the duplicate in PQ, create a calculated column in DAX. Please see sample pbix for the details.

     

    You can then use the generated columns in DAX/PQ to create your card with a formula similar to below.

    Same Food = 
CALCULATE ( COUNTROWS ( TablePQOnly ), TablePQOnly[All True] = TRUE () )

     

    PBIX - https://drive.google.com/file/d/1VDFLIOpShuC3TjJEQkqBclwBPL0ydQov/view?usp=sharing