Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
OK. In that case I will use a table
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
Thank you very much, Marcel.
That's what I was looking for.
"
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?
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:
"
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.
OK. In that case I will use a table
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
The new split by chars into Rows could be useful here for detecting repeat characters.
Phil: I guess you mean the Split Column into Rows?
In general I think List operations are more efficient than table operations.
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |