Count number of similar elements in columns.

[Miles1987]

Hello, 

I am having problems solving the issue below

I have a table with these columns, where I am trying to calculate the one called "Wanted result" in PQ. but have not succeeded yet. Basically, I am trying to count how many of the in IDs I column “Employee id” appear in column “Former Employee id “. Without creating more duplicate rows in EOM column.

EOM	Employee id	Former Employee id	Wanted result
31. december 2019	W00009	W00008	0
31. maj 2020	W00062	W00062	1
31. marts 2022	W00062,W23166	W00062	1
31. december 2021	W00090	W00090	1
31. juli 2021	W00090	W00090,W21939	1
31. maj 2021	W00090	W00091,W25560	0
31. december 2020	W00090	W00260	0
31. januar 2022	W00090,W21939	W00090	1
31. juli 2022	W00090,W21939	W00090,W21939	2

Best regards Jesper

[Vijay_A_Verma · Accepted solution]

Use below formula

= List.Count(List.Intersect({Text.Split([Former Employee id],","),Text.Split([Employee id],",")}))

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc89DsMgDAXgq0TMKLKNgspJMkQZaMNQVDrQ5P4hyo9RKEyW3ieePQxCYdtM7uXC08WGAI2Qoof0ruEhRrm7YH0iBEeiiQcmcf5tiDPZk0KtS5vVEh6xAR5O55fPu2LS32iUuW9YSkyy6zT8LYcbp8x5+11szC/i1uqmdXotPK4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EOM = _t, #"Employee id" = _t, #"Former Employee id" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Count(List.Intersect({Text.Split([Former Employee id],","),Text.Split([Employee id],",")})), Int64.Type)
in
    #"Added Custom"