Merging column

Hello Nagababu 
As you've mentioned above result table;

i.e

expecting out put is below wise

Have doubt in the row

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

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.

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

thanks,
Nagababu

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