Peter Bartholomew
Well-Known Member
BTW, analysis of the formula approach shows that I have split the number into its constituent digits 18 times. A good candidate for a helper range perhaps?
>>> import itertools
>>> list(set(itertools.combinations([1,2,3,4],2)))
[(1, 2), (1, 3), (1, 4), (2, 3), (3, 4), (2, 4)]
>>> list(set(itertools.combinations([5,6,6,8],2)))
[(5, 6), (6, 8), (5, 8), (6, 6)]
>>> list(set(itertools.combinations([5,7,7,7],2)))
[(5, 7), (7, 7)]
>>> list(set(itertools.combinations([7,7,7,7],2)))
[(7, 7)]
Like your doc man!The uploaded workbook contains both a formula solution and a PQ solution but with different limitations and characteristics.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Original Numbers", Int64.Type}}),
AddIndex = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
GroupRows = Table.Group(AddIndex, {"Index"}, {{"AsTable", each _, type table}}),
ConvertToTable = Table.AddColumn(GroupRows, "AsSingleColumnTable", each Table.FromList(Table.Column([AsTable],"Original Numbers"), Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
TransposeTable = Table.AddColumn(ConvertToTable, "TransposeTable", each Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes([AsSingleColumnTable], {{"Column1", type text}}, "nl-BE"), {{"Column1", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1")),
AddSubIndex0 = Table.AddColumn(TransposeTable, "AddIndex0", each Table.AddIndexColumn([TransposeTable],"Index0",0,1)),
AddSubIndex1 = Table.AddColumn(AddSubIndex0, "AddIndex1", each (Table.AddIndexColumn([AddIndex0],"Index1",1,1))),
MergeTable = Table.AddColumn(AddSubIndex1, "MergedSubTable", each Table.NestedJoin([AddIndex0], "Index0", [AddIndex1], "Index1", "MergedTable", JoinKind.LeftOuter))
in
MergeTable
An amazing piece of ingenuity, there should be a prize!A self-contained formula
I: ={1,2,3,4,5,6,7,8,9}
J: =TRANSPOSE( I )
X: = 10*I + J
Y: = TRANSPOSE( X )
Σ: = X + Y
Δ: = ABS( X - Y )
M: = ( Σ - Δ ) / 2
Hi Lori, it is where I'm stuck too. There is a way. I was thinking of using the modulo and replacing the value by a parameter based on a row count. Did not find the time to try and got errors all over.I'm working through the power query solutions about which I am pretty clueless. To create the table of pairs, i was wondering if there was a way to do a cross join to generate a list of n x n entries like adding the same table twice to a sql query?
MergeTable = Table.AddColumn(AddXJoinIndex1, "MergedSubTable", each Table.NestedJoin([AddXjoin0], {"Index0X", "Index0"}, [AddXjoinIndex], {"Index1X", "Index1"}, "MergedTable", JoinKind.FullOuter))
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Original Numbers", Int64.Type}}),
AddIndex = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
GroupRows = Table.Group(AddIndex, {"Index"}, {{"AsTable", each _, type table}}),
ConvertToTable = Table.AddColumn(GroupRows, "AsSingleColumnTable", each Table.FromList(Table.Column([AsTable],"Original Numbers"), Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
TransposeTable = Table.AddColumn(ConvertToTable, "TransposeTable", each Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes([AsSingleColumnTable], {{"Column1", type text}}, "nl-BE"), {{"Column1", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1")),
AddSubIndex0 = Table.AddColumn(TransposeTable, "AddIndex0", each Table.AddIndexColumn([TransposeTable],"Index0",0,1)),
AddXJoinIndex0 = Table.AddColumn(AddSubIndex0, "AddXjoin0", each Table.AddIndexColumn([AddIndex0],"Index0X",1,0)),
AddSubIndex1 = Table.AddColumn(AddXJoinIndex0, "AddIndex1", each (Table.AddIndexColumn([TransposeTable],"Index1",1,1))),
AddXJoinIndex1 = Table.AddColumn(AddSubIndex1, "AddXjoinIndex", each Table.AddIndexColumn([AddIndex1],"Index1X",1,0)),
MergeTable = Table.AddColumn(AddXJoinIndex1, "MergedSubTable", each Table.NestedJoin([AddXjoin0], {"Index0X", "Index0"}, [AddXjoinIndex], {"Index1X", "Index1"}, "MergedTable", JoinKind.FullOuter)),
#"Removed Columns" = Table.RemoveColumns(MergeTable,{"AsTable", "AsSingleColumnTable", "TransposeTable", "AddIndex0", "AddXjoin0", "AddIndex1", "AddXjoinIndex"}),
#"Expanded MergedSubTable" = Table.ExpandTableColumn(#"Removed Columns", "MergedSubTable", {"Column1", "MergedTable"}, {"Column1", "MergedTable"}),
#"Expanded MergedTable" = Table.ExpandTableColumn(#"Expanded MergedSubTable", "MergedTable", {"Column1"}, {"Column1.1"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded MergedTable", each ([Column1] <> null) and ([Column1.1] <> null)),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Pairs", each [Column1.1]&[Column1]),
#"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Pairs", "Index"}),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Duplicates",{"Column1", "Column1.1"})
in
#"Removed Columns1"
import xlwings as xlw
import itertools
@xlw.func
@xlw.arg('num',numbers = int)
def listcombs(vals, num=2):
return list(set(itertools.combinations(vals, num)))
I fail to turn this into a function (if someone has a tip, highly appreciate that), but I believe the following transformation steps also work.I think I have done it! (I have updated my workbook from #25.)
let
Source = Excel.CurrentWorkbook(){[Name="t4fct"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Original Numbers", type text}}),
AsTable = Table.AddColumn(#"Changed Type", "AsTable", each Table.FromList({[Original Numbers]},Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
SplitTable = Table.AddColumn(AsTable, "SplitTable", each Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes([AsTable], {{"Column1", type text}}, "nl-BE"), {{"Column1", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1")),
DistinctTable = Table.AddColumn(SplitTable, "DistinctTable", each Table.Distinct([SplitTable],{"Column1"})),
AddXjoin = Table.AddColumn(DistinctTable, "AddXjoin", each Table.AddIndexColumn([DistinctTable],"IndexX",1,0)),
AddIndex = Table.AddColumn(AddXjoin, "AddIndex", each Table.AddIndexColumn([AddXjoin],"Index",1,1)),
MergeTable = Table.AddColumn(AddIndex, "MergeTable", each Table.NestedJoin([AddIndex], {"IndexX"}, [AddIndex],{"IndexX"},"MergeTable" ,JoinKind.FullOuter)),
CountRows = Table.AddColumn(MergeTable, "Counter", each Table.RowCount([MergeTable])),
ExpandMergeTable1 = Table.ExpandTableColumn(CountRows, "MergeTable", {"Column1", "Index", "MergeTable"}, {"Column1", "Index", "MergeTable.1"}),
ExpandMergeTable2 = Table.ExpandTableColumn(ExpandMergeTable1, "MergeTable.1", {"Column1", "Index"}, {"Column1.1", "Index.1"}),
MakePairs = Table.AddColumn(ExpandMergeTable2, "Pairs", each if [Counter] = 1 then [Column1]&[Column1.1] else if [Index.1] > [Index] then [Column1]&[Column1.1] else null),
FilterNull = Table.SelectRows(MakePairs, each ([Pairs] <> null)),
RemoveColumns = Table.RemoveColumns(FilterNull,{"AsTable", "SplitTable", "DistinctTable", "AddXjoin", "AddIndex", "Column1", "Index", "Column1.1", "Index.1", "Counter"})
in
RemoveColumns
You are ahead of me in terms of the sophistication of your use of PQ. I think you have kept tables (how is that done? is it by typing a line within the advanced editor or is there a button I haven't noticed?) where I keep on loading as 'connection only' to retain partial results. Your outer join looks more compact than anything I have attempted.I believe the following transformation steps also work
The download file should just work, once Python and xlwings are installed, but I am happy to answer questions.Aside: The Python and xlwings looks interesting but might need further description and I am not sure where such a discussion might fit?
I was wondering whether the 'vault' might be an appropriate sub-forum. The issues raised clearly go beyond the scope of this discussion thread that addresses combinations of digits. I would like to be able to be able to refer to the instructions and relevant advice when I am in a position to devote time to the task of implementing and learning something new.It's a simple to implement way of extending Excel, using free software
I think functionality from PQ is within the Excel Object model so it should be possible to implement an event-driven update (maybe not in my 2010 PQ add-in though). I must admit I find the expectation that one should hit refresh something of a drawback when one is simply responding to a worksheet change.PQ resizes the table but needs a manual refresh and formulas automatically calculate but don't resize