Create a custom column that evaluates data in 3 different columns and returns the common value

Hi Anonymous ,

It's better to do this transformation in Power Query and find the common values form your three columns. Here's my M code :

let
Source = Excel.Workbook(File.Contents("C:\Users\aliom\OneDrive\Power BI Samples\Extract Commom String\Sample.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Job Title", type text}, {"Shift_Code_S", type text}, {"Shift_Code_O", type text}, {"Shift_Code_D", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Raw_Shift Code (Desired Result)"}),
// Custom function to find the common string prefix
FindCommonPrefix = (s1 as text, s2 as text, s3 as text) as text =>
let
len = List.Min({Text.Length(s1), Text.Length(s2), Text.Length(s3)}),
commonPrefixList = List.Select({0..len - 1}, each Text.Start(s1, _ + 1) = Text.Start(s2, _ + 1) and Text.Start(s1, _ + 1) = Text.Start(s3, _ + 1)),
commonPrefix = if List.IsEmpty(commonPrefixList) then "" else Text.Start(s1, List.Max(List.Transform(commonPrefixList, each _ + 1)))
in
commonPrefix,
// Add a Custom Column using the custom function
#"Added CommonCode" = Table.AddColumn(#"Removed Columns", "CommonCode", each FindCommonPrefix([Shift_Code_S], [Shift_Code_O], [Shift_Code_D]), type text)
in
#"Added CommonCode"

You can download my test files form this link:

https://1drv.ms/f/s!Aq3n-sopiGyqgolf8Zmm9dD2akM7wg?e=t2a9L4

If I answered your question, please mark this thread as accepted and Thums Up!
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/