Check if all values in a row are the same

[Jocky]

Hi there,

I am presuming this is simple, but I can't seem to find the right way to do it.

I have a dataset that looks like the following.

Name

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Bob

A

A

A

A

A

A

A

A

A

A

B

B

John

G

G

G

G

G

G

G

G

G

G

G

G

Sally

D

D

D

D

D

D

C

C

C

C

C

C

Sarah

A

A

A

A

A

A

A

A

A

A

A

A

Craig

C

B

B

B

B

B

B

B

B

B

B

B

What I want to determine is if the individual has the same letter against their name throughout all months.  This either be by a simple yes/no (or true/false) or by how many times the value is different.

Name

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Are all values the same?

How many different values?

Bob

A

A

A

A

A

A

A

A

A

A

B

B

No

2

John

G

G

G

G

G

G

G

G

G

G

G

G

Yes

1

Sally

D

D

D

D

D

D

C

C

C

C

C

C

No

2

Sarah

A

A

A

A

A

A

A

A

A

A

A

A

Yes

1

Craig

C

B

B

B

B

B

B

B

B

B

B

B

No

2

Any idea what i would use to achieve this?

Thanks

[Vijay_A_Verma · Accepted solution]

Use these formulas

= [Lst=List.RemoveFirstN(Record.ToList(_),1),
r=if List.IsEmpty(List.RemoveItems(Lst,{List.First(Lst)}))then "Yes" else "No"][r]

= List.Count(List.Distinct(List.RemoveLastN(List.RemoveFirstN(Record.ToList(_),1),1)))

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRciQSO4FxrE60kld+Rh6Q404SBmkMTszJqQTyXHBgZ6wYorMoMYMEx0IwSKdzUWJmOtQsJ6JxbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Jan = _t, Feb = _t, Mar = _t, Apr = _t, May = _t, Jun = _t, Jul = _t, Aug = _t, Sep = _t, Oct = _t, Nov = _t, Dec = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Are all values the same?", each [Lst=List.RemoveFirstN(Record.ToList(_),1),
r=if List.IsEmpty(List.RemoveItems(Lst,{List.First(Lst)}))then "Yes" else "No"][r]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "How many different values?", each List.Count(List.Distinct(List.RemoveLastN(List.RemoveFirstN(Record.ToList(_),1),1))))
in
    #"Added Custom1"