Finally @Peter Bartholomew, I have finished my bumpy quest to find the "one stop list function" in Power Query. So to all it might be of interest read along please.I have only ever written one function by following step-by-step guidance so I have nothing to offer at present on that front.
One thing you might consider borrowing from my attempt is the test for multiple occurrences of a digit which you are currently filtering out unless it happens to be the only digit used for the string.
let GetPairs = (TxtVar as text) =>
let StrLen = Text.Length(TxtVar)-1,
MakePairs =
List.RemoveNulls (
List.Distinct (
List.Generate( ()=> [i=0, j=0, ListOut=null],
each [i] <= StrLen,
each if [j] < StrLen-([i]+1) then
[i=[i], j=[j]+1, ListOut= Text.Middle(TxtVar,[i],1) & Text.Middle(Text.End(TxtVar,StrLen - [i]),[j],1)]
else [i=[i]+1, j=0,ListOut= Text.Middle(TxtVar,[i],1) & Text.Middle(Text.End(TxtVar,StrLen - [i]),[j],1)],
each [ListOut])))
in MakePairs
in GetPairs
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Values", type text}}),
CallFxGetPairs = Table.AddColumn(#"Changed Type", "GetPairs", each FxGetPairs([Values]))
in
CallFxGetPairs
let
Source = (inputNum as any) => let
Source = {1..inputNum},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "num1"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "num2", each {1..[num1]-1}),
#"Expanded to Combine" = Table.ExpandListColumn(#"Added Custom", "num2"),
#"Filtered Rows" = Table.SelectRows(#"Expanded to Combine", each ([num2] <> null))
in
#"Filtered Rows"
in
Source
let
Source = (thisNumber as any) => let
Source = thisNumber,
#"Converted to Table" = #table(1, {{Source}}),
#"Inserted Text Length" = Table.AddColumn(#"Converted to Table", "Length", each Text.Length(Text.From([Column1], "en-NZ")), type number),
#"Invoked Custom Function" = Table.AddColumn(#"Inserted Text Length", "combinations", each getCombinations([Length])),
#"Expanded combinations" = Table.ExpandTableColumn(#"Invoked Custom Function", "combinations", {"num1", "num2"}, {"num1", "num2"}),
#"Inserted Text Range" = Table.AddColumn(#"Expanded combinations", "Text Range", each Text.Middle(Text.From([Column1], "en-NZ"), [num1]-1, 1) & Text.Middle(Text.From([Column1], "en-NZ"), [num2]-1, 1), type text),
#"Removed Duplicates" = Table.Distinct(#"Inserted Text Range", {"Text Range"}),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Duplicates",{"Text Range"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Text Range", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Text Range", "numbers"}})
in
#"Renamed Columns"
in
Source
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Text Range", Int64.Type}}),
What about uneven list of numbers? EG. {123, 12345, ...}. Or are you just asking for NNNN list?Hello friends ,
Number with 4 digits ,you have to find pairs,A formula that suits all 4 different situations
for example
The number ,1234 there are 6 pairs ,{12;13;14;23;24;34}
The number ,5668 there are 4 pairs ,{56;58;66;68}
The number ,5777 there are 2 pairs,{57;77}
The number ,7777 there are 1 pair, {77}
You can start the solution, from first row, or second row,
Conditioning is, without volatile functions ,And NO UDF !
David