Filter Rows Identical Content Across Columns

[wsMac78AZ]

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.

	Breakfast	Lunch	Dinner	Supper
Monday	Cereal	Steak	Soup	Biscuit
Tuesday	Soup	Soup	Soup	Soup
Wednesday	Cereal	Spaghetti	Spaghetti	Soup
Thursday	Soup	Steak	Curry	Cake
Friday	Cake	Cake	Cake	Cake
Saturday	Porridge	Porridge	Soup	Spaghetti
Sunday	Soup	Spaghetti	Steak	Soup

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

Tuesday	Soup	Soup	Soup	Soup
Friday	Cake	Cake	Cake	Cake

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

2

Same food

Would appreciate any help in this regard. Thanks!

[CNENFRNL]

Simple enough,

PQ solution,

DAX solution,

For fun only, showcase of powerful Excel worksheet formula,

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[johncolley · Accepted solution]

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.

[wsMac78AZ]

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

[danextian]

Well, this is a much simpler solution 🙂

[danextian]

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