Count unique values with the same prefix (integer) concatenated with different t

Q. How to count unique values with the same prefix (integer) concatenated with different text values, display the unique records, count of unique records and count of records with unique prefix integer but different txt ?

Example: Below table of system users have unique User ID: 43871Wally, 43871Daniel, 43871Craig - All these users have the same prefix 43871. And There are few duplicate entries with the same prefix 33871.
Note: There is no space in the original data between Integer and Name combines forms UserID.

Output Required: A) 43871Wally are unique entries should be populated in a separate column B) unique count of these entries should be calculated = 3 C) total count of entries "43871" should be calculated = 5.

Refer sample data below of the table.


43871Wally
43871Wally
43871Wally
43872David
43873Michael
43871Daniel
43871Craig
43872Paul

This is simple, using Power Query:

Distinct Values:

Please Login or Register  to view this content.

Distinct Prefixes:

Please Login or Register  to view this content.

I'm not entirely clear about what you are expecting to see and where you expect to see it.


Please read the yellow banner at the top of the screen. Act on its guidelines and post a SMALL sample sheet.