Forum Discussion

vvSTRIDEvv's avatar
vvSTRIDEvv
Frequent Visitor
7 years ago

Create Unique List Across Columns (Index/Match/If/Countif)

I'm trying to replicate the following, where I had been using the index/match/contif combo in Excel.

 

Table: Labor (For each employee added to a receipt in our system, a seperate row)

ReceiptAssociateName
1258611276Employee 1
1258611391Employee 2
1258611501Employee 3

 

I need to display this data in a Power BI table, in the following format:

1. No duplicate receipt ids (I have them coming from a related table without dups so no big)

2. Return the Name(s) for the given Receipt id, accross the 4 columns.

ReceiptAssoc. #1Assoc. #2Assoc. #3Assoc. #4
125861Employee 1Employee 2Employee 3 

 

Here is the Excel Formula that would be in B2 and copied right.

INDEX(Labor!$C:$C,MATCH(0,IF($A1=Labor!$A:$A,COUNTIF($A2:A2,Labor!$C:$C),""),0))

 

Possible? Need more info?

 

Thanks in advance.

1 Reply

  • vvSTRIDEvv's avatar
    vvSTRIDEvv
    Frequent Visitor

    If anyone has any resources on this, it would be greatly appreciated. Thanks