Forum Discussion

Jocky's avatar
Jocky
Frequent Visitor
4 years ago
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 ...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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"