Forum Discussion
Solved
Check if all values in a row are the same
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
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"
1 Reply
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"