Add Custom Column to check for the same digits in a cell

[programsam]

Greetings, attempting to add a custom column in PQ that runs down a column and if the column has numbers of the same value in them it marks the custom column with an "X".

In the screenshot below, the first and third numbers have the same digits in the cells so it would mark an "X" in the custom column.

 

[jerry12302]

Assuming the value you are testing is in cell A1, for example, change reference as necessary...

For the test: first three digits are the same...

=if(and(left(text(A1,"####"),1)=mid(text(A1,"####"),2,1),left(text(A1,"####"),1)=mid(text(A1,"####"),3,1)),"X","")

For the test: first and third digits are the same... (as in your question)

=if(and(left(text(A1,"####"),1)=mid(text(A1,"####"),3,1)),"X","")

 

[smozgur]

One way of doing this is creating a list from the whole number by using the Text.ToList() function, then using List.Distinct() function to return only distinct values from this list, and counting the list items by using the List.Count() function. If the result is 1 then it means all digits are the same and you can return "X", otherwise return null or empty string or something else you desire. See the following sample query (copy and paste it into a blank query in PQ advanced editor). I used manually entered data as sample, so you can safely ignore the Source and ChangeTypes steps, and focus on AddColumn step by using your own Source data and column names.

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsoQDpVidaCVDI2MTUzNzCwjPFA6UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
  ChangeTypes = Table.TransformColumnTypes(Source, {{"Column1", Int64.Type}}),
  AddColumn = Table.AddColumn(ChangeTypes, "Check", 
                each if List.Count(
                      List.Distinct(
                        Text.ToList(Text.From([Column1]))
                    )
                  ) = 1 then "X" else null
                )
in
  AddColumn