Is there a way to look up another column should there be no match in the first one using Power BI?

bixby 0
Viewed 56

I have 2 tables, 1 contains the member data and the other is the transaction table. The members has different member IDs with different information on each IDs, but I only look up the member ID we consider as unique. Now the problem is, in the transactions table, it sometimes records the transaction under the other member's ID. Here's a table to further illustrate the situation:

TABLE = MEMBER_DATA

MEMBER_ID LINKED_ID FNAME LNAME MEMBER_TYPE TIER
8888-1234 X1234 JOHN SMITH REGULAR -
7777-3333 X1234 JOHN SMITH - GOLD
8888-6666 X0993 HANNA MONTANA REGULAR -
7777-9999 X0993 HANNA MONTANA - PLATINUM

TABLE = TXN_TABLE

MEMBER_ID TXN_COUNT AMOUNT
8888-1234 1 3
8888-6666 2 4
7777-9999 2 2

RESULT:

MEMBER_TYPE MEMBER_ID TXN_COUNT
REGULAR 8888-1234 1
REGULAR 8888-6666 2

The member is assigned a MEMBER_ID known to them that starts with 8888, for us, there is another ID assigned to them for their other information that starts with 7777 (it's already that way when I got here so no possibility of change). The way we know they are the same person is because of the LINKED_ID. I only looked at MEMBER_IDs with MEMBER_TYPE = REGULAR to count the members. In the transactions table, it sometimes records the transaction to the member's other ID so I don't get an accurate count of transactions. The solution I came up with is to transform the MEMBER_DATA table to add their other MEMBER_ID and other information.

The question is, how now do I look up the MEMBER_ID2 if the TXN_TABLE MEMBER_ID doesn't match MEMBER_ID1?

TRANSFORMED TABLE:

TABLE = TRANSFORMED_MEMBER_DATA

MEMBER_ID1 LINKED_ID FNAME LNAME MEMBER_TYPE TIER MEMBER_ID2 TIER
8888-1234 X1234 JOHN SMITH REGULAR - 7777-3333 GOLD
7777-3333 X1234 JOHN SMITH - GOLD - -
8888-6666 X0993 HANNA MONTANA REGULAR - 8888-6666 PLATINUM
7777-9999 X0993 HANNA MONTANA - PLATINUM - -

EXPECTED:

MEMBER_TYPE MEMBER_ID TXN_COUNT TIER
REGULAR 8888-1234 1 GOLD
REGULAR 8888-6666 4 PLATINUM

Note: Relationship of TXN_TABLE and MEMBER_DATA is on column MEMBER_ID

powerbi-datasource powerbi
1 Answers

For a Power Query solution:

  • Join the two tables based on MEMBER_ID
  • Group by LINKED_ID
  • Aggregate by MAX except for the TXN_COUNT which you aggregate by SUM
  • Remove the LINKED_ID column

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="MEMBER_DATA"]}[Content],
    MEMBER_DATA = Table.TransformColumnTypes(Source,{
        {"MEMBER_ID", type text}, {"LINKED_ID", type text}, {"FNAME", type text},
        {"LNAME", type text}, {"MEMBER_TYPE", type text}, {"TIER", type text}}),

    Source2 = Excel.CurrentWorkbook(){[Name="TXN_TABLE"]}[Content],
    TXN_TABLE = Table.TransformColumnTypes(Source2,{{"MEMBER_ID", type text}, 
        {"TXN_COUNT", Int64.Type}, {"AMOUNT", Int64.Type}}),

//Join the tables
    Joined = Table.NestedJoin(MEMBER_DATA,"MEMBER_ID", TXN_TABLE, "MEMBER_ID", "joined", JoinKind.FullOuter),
    #"Expanded joined" = Table.ExpandTableColumn(Joined, "joined", {"TXN_COUNT"}, {"TXN_COUNT"}),
    #"Grouped Rows" = Table.Group(#"Expanded joined", {"LINKED_ID"}, {
        {"MEMBER_TYPE", each List.Max([MEMBER_TYPE]), type nullable text}, 

    //This next works because '8888' > '7777'
    //  if your assigned ID's have a different pattern, may need to change this
        {"MEMBER_ID", each List.Max([MEMBER_ID]), type nullable text},
         
        {"TXN_COUNT", each List.Sum([TXN_COUNT]), type nullable number}, 
        {"TIER", each List.Max([TIER]), type nullable text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"LINKED_ID"})
in
    #"Removed Columns"

Results from your data above
enter image description here

Ron Rosenfeld0
Related