Merging column

[Nagababu]

Hi Team,

I have a requirement for merging, and I've tried a couple of approaches, but they haven't met the expected output. Could someone please assist me with this requirement?

Customer Employee ID when available; otherwise,  Employee ID is used.
If multiple  Employee IDs have the same Customer Employee ID, they are merged only if their first name or last name match exactly.

first condition is working fine.

sample data 

customer_employee_id	theatro_employee_id	first_name	last_name
1	101	John	Doe
1	102	John	Doe
1	103	Spin	george
1	104	Jane	Doe
1	105	Mary	Doe
1	106	John	Smith
1	107	John	Doe
1	108	John	Doe
2	109	Jane	Smith
3	110	John	Doe

expecting out put is below wise

customer_employee_id	theatro_employee_id	first_name	last_name	MergeFlag
1	101	John	Doe	1
1	102	John	Doe	1
1	103	Spin	george	103
1	104	Jane	Doe	1
1	105	Mary	Doe	105
1	106	John	Smith	106
1	107	John	Doe	1
1	108	John	Doe	1
2	109	Jane	Smith	109
3	110	John	Doe	110

[sanalytics]

@Nagababu 
According to your condition Jane doe should be 104.. @rohit1991 and @BIswajit_Das  are correct..
I have also checked in Power query also and below is the code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0AJFe+Rl5QMolP1UpVgcmYYRLwhhIBhdkgiTSU/OL0pHlTECaEvNSMTSZAknfxKJKDAkzhDXBuZklGUhS5rhcYIEpYQSWsERYjzAM5F5DQwM0PbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [customer_employee_id = _t, theatro_employee_id = _t, first_name = _t, last_name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"customer_employee_id", Int64.Type}, {"theatro_employee_id", Int64.Type}, {"first_name", type text}, {"last_name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"first_name", "last_name", "customer_employee_id"}, {{"Count", each _, type table [customer_employee_id=nullable number, theatro_employee_id=nullable number, first_name=nullable text, last_name=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
            innerTable = [Count],
            rowCount = Table.RowCount(innerTable)
         in
            Table.AddColumn(innerTable, "check", each rowCount)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"customer_employee_id", "theatro_employee_id", "first_name", "last_name", "check"}, {"customer_employee_id", "theatro_employee_id", "first_name", "last_name", "check"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "MergeFlag", each if [check] > 1 then [customer_employee_id] else [theatro_employee_id]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"check"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"theatro_employee_id", Order.Ascending}})
in
    #"Sorted Rows"

the same logic i have applied in DAX as well

MergeFlag = 
VAR _tag =CALCULATE(
    COUNT('Table'[customer_employee_id] ),
    ALLEXCEPT( 'Table','Table'[first_name],'Table'[last_name], 'Table'[customer_employee_id] )
)
VAR _MergeFlag = 
IF(
    _tag >1,[customer_employee_id],[theatro_employee_id]
)
RETURN
_MergeFlag

Below is the pbix file.

Regards

sanalytics

[Nagababu]

Thank you for providing the sample dashboard. As expected, the output is as anticipated, but there are still a few records that are not falling under the Flag column. According to the logic, for example, if the name is different but the customer ID remains the same, it is still considering the customer ID instead of the theater ID. Any way thanks.

[Nagababu]

Thanks for responding .
that is 1 in merged flag  not 104. As of now i have only that sample data .

thanks,
Nagababu

[rohit1991]

Hi @Nagababu ,

To achieve the expected output, we need to merge theatro_employee_id values based on specific conditions. The merging should occur when multiple theatro_employee_ids share the same customer_employee_id, but only if their first_name and last_name match exactly. Otherwise, unique records should retain their own theatro_employee_id as the MergeFlag.

The first step is to identify groups of records with the same customer_employee_id. Within each group, if multiple records have the same first_name and last_name, they should be assigned the same MergeFlag, which is the minimum theatro_employee_id within that group. If a record has a unique first_name and last_name, its own theatro_employee_id should be used as its MergeFlag.

For example, in the provided data:

• customer_employee_id = 1 has multiple entries for "John Doe" (101, 102, 107, 108), so all of them receive MergeFlag = 101 (the lowest theatro_employee_id in the group).
• "Spin George" does not have duplicates, so it retains 103 as its MergeFlag.
• "Jane Doe" has only one entry, so it gets MergeFlag = 104.
• "Mary Doe" is unique, so it gets MergeFlag = 105.
• "John Smith" is unique, so it gets MergeFlag = 106.
• For customer_employee_id = 2 and 3, each entry is unique and retains its theatro_employee_id.

[Nagababu]

Thanks  for responding rohit1991

[BIswajit_Das]

Hello @Nagababu 
As you've mentioned above result table;

i.e

expecting out put is below wise

customer_employee_id	theatro_employee_id	first_name	last_name	MergeFlag
1	101	John	Doe	1
1	102	John	Doe	1
1	103	Spin	george	103
1	104	Jane	Doe	1
1	105	Mary	Doe	105
1	106	John	Smith	106
1	107	John	Doe	1
1	108	John	Doe	1
2	109	Jane	Smith	109
3	110	John	Doe	110

Have doubt in the row

customer_employee_id	theatro_employee_id	first_name	last_name	MergeFlag
1	104	Jane	Doe	1

the MergeFlag value here should be 1 or 104
If it's 104 then here's the DAX for creating a calculated column which return your required result
i.e

MergeFlag =

VAR _rows =

COUNTROWS(

    FILTER('TABLE',

        'TABLE'[customer_employee_id] = EARLIER('TABLE'[customer_employee_id]) &&

        'TABLE'[first_name] = EARLIER('TABLE'[first_name]) &&

        'TABLE'[last_name] = EARLIER('TABLE'[last_name])

    )

)

RETURN

IF(_rows > 1, 'TABLE'[customer_employee_id] , 'TABLE'[theatro_employee_id])

But if it's 1 then need more data for better understanding.

Thanks & Regards...