Forum Discussion
Nagababu
1 year agoNew Member
Merging column
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...
sanalytics
1 year agoSuper User
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
1 year agoNew Member
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.