Generating row value combinations

[anandav]

Hi,

I am trying to generate combination of values found in row data.

Source Data

Customer	Column2
Paul	A
Paul	B
Paul	C
Tom	D
Tom	E
Tom	F
Jerry	H
Jerry	I
Jerry	J

Expected Output

Customer	Combined Column
Paul	A-B
Paul	A-C
Paul	B-C
Tom	D-E
Tom	D-F
Tom	E-F
Jerry	H-I
Jerry	H-J
Jerry	I-J

Any suggestion on how this can be achieved either in M or DAX?

Even if the combinations are repeated, for example  A-B and B-A, I am fine with that.

Thanks in advance for your help.

Anand.

[Greg_Deckler · Accepted solution]

This seems close, albeit with duplicates:

Table = 
VAR __tmpTable1 = SELECTCOLUMNS(ALL('Data'),"__Customer",[Customer],"__Column2",[Column2])
VAR __tmpTable2 = SELECTCOLUMNS(ALL('Data'),"___Customer",[Customer],"___Column2",[Column2])
VAR __tmpTable3 = FILTER(GENERATE(__tmpTable1,__tmpTable2),[__Customer]=[___Customer]&&([__Column2]>[___Column2]||[__Column2]<[___Column2]))
VAR __tmpTable4 = ADDCOLUMNS(__tmpTable3,"__CombinedColumn",[__Column2]&"-"&[___Column2])
RETURN SELECTCOLUMNS(__tmpTable4,"Customer",[__Customer],"Combined Column",[__CombinedColumn])

[Ashish_Mathur · Accepted solution]

Hi,

See the image.  Download file from here.

[Ashish_Mathur]

Hi,

Here's the M code i used

let
    Source = Table.NestedJoin(Data,{"Customer"},#"Data (2)",{"Customer"},"Data (2)",JoinKind.LeftOuter),
    #"Expanded Data (2)" = Table.ExpandTableColumn(Source, "Data (2)", {"Column2"}, {"Column2.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data (2)", "Custom", each [Column2]&"-"&[Column2.1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column2", "Column2.1"})
in
    #"Removed Columns"

[anandav]

@Ashish_Mathur,

I downloaded the data into a table named Data.

Then from Get Source I selected Blank Query and type your M code.

But it does not create a table. I get the following:

[Ashish_Mathur · Accepted solution]

Hi,

See the image.  Download file from here.

[anandav]

@Ashish_Mathur,

That is a very simple solution using M.

Thanks a lot.

It needs some tweaking to remove same letter combination, e.g. A-A, but still very easy to achieve.

Thank you for your help.

[Ashish_Mathur]

You are welcome.

[Greg_Deckler · Accepted solution]

This seems close, albeit with duplicates:

Table = 
VAR __tmpTable1 = SELECTCOLUMNS(ALL('Data'),"__Customer",[Customer],"__Column2",[Column2])
VAR __tmpTable2 = SELECTCOLUMNS(ALL('Data'),"___Customer",[Customer],"___Column2",[Column2])
VAR __tmpTable3 = FILTER(GENERATE(__tmpTable1,__tmpTable2),[__Customer]=[___Customer]&&([__Column2]>[___Column2]||[__Column2]<[___Column2]))
VAR __tmpTable4 = ADDCOLUMNS(__tmpTable3,"__CombinedColumn",[__Column2]&"-"&[___Column2])
RETURN SELECTCOLUMNS(__tmpTable4,"Customer",[__Customer],"Combined Column",[__CombinedColumn])

[anandav · Accepted solution]

@Greg_Deckler,

Thanks to your solution I made a minor adjustment to step3. Removed "[__Column2]>[___Column2]||". And that gave the exact result I nmeeded.

VAR __tmpTable3 =
    FILTER (
        GENERATE ( __tmpTable1, __tmpTable2 ),
        [__Customer] = [___Customer]
            && ([__Column2] < [___Column2] )
    )

[anandav]

@Greg_Deckler,

That's works! Excellent solution!

Thanks a lot for the prompt reply.

I can sort out the duplicate combination.

Thanks you.

[Aree]

Not sure if this helps you or if i understand what you are attempting to achieve becuase the Combine Column values seem random.
If am wrong and they are not random i apologise.

For a given Customer Paul join the first letter of their name with the value in Column2

Combination = [Column2] &"-"& LEFT( [Customer],1 )

The result will be: A-P