Forum Discussion

anandav's avatar
anandav
Skilled Sharer
8 years ago
Solved

Generating row value combinations

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 ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    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's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    See the image.  Download file from here.

     

  • anandav's avatar
    anandav
    8 years ago

    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] )
        )

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

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's avatar
anandav
Skilled Sharer
8 years ago

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's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    See the image.  Download file from here.

     

    • anandav's avatar
      anandav
      Skilled Sharer
      8 years ago

      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's avatar
        Ashish_Mathur
        Super User
        8 years ago

        You are welcome.