Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mat87
Frequent Visitor

Is there a way to check for repeated/unique digits in a number?

Say I have a number like 9976288 and I want to return either 2 (99 and 88) or 5 (there  are overall 5 uniqe digits in the number).

 

Is there a way to do that? 

 

 

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

OK. In that case I will use a table Smiley Very Happy

And a function.

In this video you can see how it is created (although the video was created afterwards).

 

Query:

let
    Source = {"9976288","112244","123453673", "12345"},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Input"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Splitted Text", each Text.ToList([Input])),
    #"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "Counts", each Counts([Splitted Text])),
    #"Expanded Counts" = Table.ExpandRecordColumn(#"Invoked Custom Function", "Counts", {"Count Distinct", "Count Duplicated Values"}, {"Count Distinct", "Count Duplicated Values"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Counts",{"Splitted Text"})
in
    #"Removed Columns"

 

Function "Counts":

let
    Source = (PseudoParameter) => 
let
    Source = PseudoParameter,
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Grouped Rows" = Table.Group(#"Converted to Table", {"Column1"}, {{"Count", each Table.RowCount(_), type number}}),
    Result = [Count Distinct = Table.RowCount(#"Grouped Rows"),Count Duplicated Values = List.Count(List.Select(#"Grouped Rows"[Count], each _ > 1))]
in
    Result
in
    Source
Specializing in Power Query Formula Language (M)

View solution in original post

8 REPLIES 8
Mat87
Frequent Visitor

Thank you very much, Marcel. 

 

That's what I was looking for.

Mat87
Frequent Visitor

"

The count of 5 can be derived with Power Query formula

List.Count(List.Distinct(Text.ToList("9976288")))

 "

 

By the way anyway I can apply this formula in DAX?

 

 

MarcelBeug
Community Champion
Community Champion

The count of 5 can be derived with Power Query formula

List.Count(List.Distinct(Text.ToList("9976288")))

 

It is not clear what you mean by 2:

  • 2 distinct values with multiple occurrences,
  • 2 8's
  • 2 9's
  • The average number of duplicates
Specializing in Power Query Formula Language (M)

"

  • 2 distinct values with multiple occurrences,"

Yeah that what I mean. So

 

112244 would return 3

789817 would return 2

123453673 would return 1

12345 would return 0 or blank 

etc

 

Oh and thanks for the Power Query formula.

MarcelBeug
Community Champion
Community Champion

OK. In that case I will use a table Smiley Very Happy

And a function.

In this video you can see how it is created (although the video was created afterwards).

 

Query:

let
    Source = {"9976288","112244","123453673", "12345"},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Input"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Splitted Text", each Text.ToList([Input])),
    #"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "Counts", each Counts([Splitted Text])),
    #"Expanded Counts" = Table.ExpandRecordColumn(#"Invoked Custom Function", "Counts", {"Count Distinct", "Count Duplicated Values"}, {"Count Distinct", "Count Duplicated Values"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Counts",{"Splitted Text"})
in
    #"Removed Columns"

 

Function "Counts":

let
    Source = (PseudoParameter) => 
let
    Source = PseudoParameter,
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Grouped Rows" = Table.Group(#"Converted to Table", {"Column1"}, {{"Count", each Table.RowCount(_), type number}}),
    Result = [Count Distinct = Table.RowCount(#"Grouped Rows"),Count Duplicated Values = List.Count(List.Select(#"Grouped Rows"[Count], each _ > 1))]
in
    Result
in
    Source
Specializing in Power Query Formula Language (M)

The new split by chars into Rows could be useful here for detecting repeat characters.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Phil: I guess you mean the Split Column into Rows?

In general I think List operations are more efficient than table operations.

Specializing in Power Query Formula Language (M)

Oh sounds good.  I need to play with lists more.  Can you pickup repeat items in a list easily enough?  

 

I was thinking of a complicated approach using the 0/1 based dual index columns merged back to identify these instances, but it seemed like overkill.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.