Check for duplicates in table rows with DAX or Power Query

[macfuller]

I have a function from the web that generates combinations from a list. In this case, we have a price list on almost 2,000 items from 19 vendors giving their prices if they are one of 3 vendors. (We also have a price list if we go with 5 and 8 vendors - much bigger.) The fewer vendors we agree to do business with for certain products, the better the price on each product from the vendor. So I generate a list of combinations using the code at the end of the post.

3V_00001	Centinel Spine
3V_00001	Depuy Synthes
3V_00001	Globus Medical
3V_00002	Centinel Spine
3V_00002	Depuy Synthes
3V_00002	K2M

and so on for all the combinations.

My question is whether I can tell if the function I use to generate the 3-vendor combinations is returning the same vendor (e.g. COMBIN vs. COMBINA in the Excel functions). For example, is combination 3V_00056 giving me Depuy, Depuy, Depuy? I would need to exclude any combination where the vendors are not unique. I'm not sure how to proceed.

I can generate a CONCATENATEX function to give me another column with all the names in the combination as below, but I'm not clear on how I could write a DAX function to confirm if the same vendor name shows up.

3V_00001	Centinel Spine	Centinel Spine,Depuy Synthes,Globus Medical
3V_00001	Depuy Synthes	Centinel Spine,Depuy Synthes,Globus Medical
3V_00001	Globus Medical	Centinel Spine,Depuy Synthes,Globus Medical
3V_00002	Centinel Spine	Centinel Spine,Depuy Synthes,K2M
3V_00002	Depuy Synthes	Centinel Spine,Depuy Synthes,K2M
3V_00002	K2M	Centinel Spine,Depuy Synthes,K2M

The best option would be to adapt the PQ function for generating a combination to ensure there are no duplicates (or to understand it well enough that I could parameterize it one way or the other <g>), but although it works it's way above my level of understanding. I use it to create all possible combinations and then filter to just give the rows with the number of combinations I want - there's probably a better way.

// From https://social.technet.microsoft.com/Forums/windows/en-US/72bbaf95-9228-4474-a59f-d3c573a75d5b/power-query-to-generating-all-combinations-from-single-column?forum=powerquery

//    hardcoded column in List.Repeat  {[Vendors]} where Vendors is the column name

(t as table, col as text, optional combo as number) as table =>

    //Do not use for more then 19 values if you want to get ResultTable in excel sheet due to rows limit in excel
    // it is over half a milion rows for 19 values
    let


        AddIndex = Table.AddIndexColumn(t, "Index", 0, 1),
        Max = Table.RowCount(t),
        ComboLimit = if combo = null then null else combo,
        ReverseIndex = Table.AddIndexColumn(AddIndex, "RevIdx", Max, -1),
        Lists = Table.AddColumn(ReverseIndex, "lists", each 
                          List.Repeat(
                                       List.Combine(
                                                     {
                                                        List.Repeat({Record.Field(_, col)}, Number.Power(2,[RevIdx]-1))
                                                       ,List.Repeat(   {null}, Number.Power(2,[RevIdx]-1))
                                                     }
                                                   )
                                       , Number.Power(2, [Index]))
                                     ),
        ResultTable = Table.FromColumns(Lists[lists]),

//  Check if a limit on combinations was requested
        AddCountCol = Table.AddColumn(ResultTable, "Distinct Values", each List.NonNullCount(List.Distinct(Record.FieldValues(_))), Int64.Type),
        FilterCombos = Table.SelectRows(AddCountCol, each ([Distinct Values] = ComboLimit)),
        ZapColumn = Table.RemoveColumns(FilterCombos,{"Distinct Values"}),

        CheckFilter = if ComboLimit is null then ResultTable
              else ZapColumn
    in
        CheckFilter

 

[macfuller]

I was overthinking it... I think this answer works.

[Is Duplicate] =
VAR num_vendors = 3
VAR my_combo = '3V Combinations'[3V Combinations]
VAR my_vendor = '3V Combinations'[3V Vendor]
VAR my_values =
    CALCULATE (
        COUNTROWS ( '3V Combinations' ),
        FILTER (
            ALL ( '3V Combinations' ),
            '3V Combinations'[3V Combinations] = my_combo
                && '3V Combinations'[3V Vendor] <> my_vendor
        )
    )
VAR is_dupe =
    IF (
        my_values < num_vendors - 1,
        "Duplicate",
        BLANK ()
    )
RETURN
    is_dupe

I'd still like to be able to modify the Power Query function though - it seems wasteful to have to generate every possible combination and then filter for the value I want.