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

[work_1111]

Hello All - I have a data set that includes shfit codes that all have an extra letter or two based on the type of shift, and I am trying to determine the raw shift code that is present in all 3 columns and put it into a "raw shift code" column. Does anyone have any ideas? I've tried length split but since the data is not a consistent set length, it doesn't work. Please see sample data and desired result example below. Note, There are lots of other columns in the table, just including the pertinent ones for this.

Current State

Job Title	Shift_Code_S	Shift_Code_O	Shift_Code_D	Raw_Shift Code (Desired Result)
Waiter	LCFNAPS	LCFNAPO	LCFNAPD	LCFNAP
Server	8147R	8147RO	8147RP	8147R
Janitor	FOHG24AFTLS	FOHG24AFTLH	FOHG24AFTLP	FOHG24AFTL
Janitor	FOHG24AFTLSN	FOHG24AFTLHN	FOHG24AFTLPN	FOHG24AFTL

[amustafa]

Yes, you need to change null to " " (single space for blank) fo rtext columns and 0 for numeric data to avoid this error. See my updated .pbix file.

[work_1111]

@amustafa THANK YOU!

[amustafa]

See the updated pbix file in my shared drive. Here's the updated M code.

let
    Source = Excel.Workbook(File.Contents("C:\\Users\\aliom\\OneDrive\\Power BI Samples\\Extract Commom String\\Sample.xlsx"), null, true),
    Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Job Title", type text}, {"Shift_Code_S", type text}, {"Shift_Code_S_Price", type number}, {"Shift_Code_O", type text}, {"Shift_Code_O_Price", type number}, {"Shift_Code_D", type text}, {"Shift_Code_D_Price", type number}, {"Raw_Shift Code (Desired new column)", type text}, {"Location", type text}, {"Vendor", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Raw_Shift Code (Desired new column)"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Vendor", "Location", "Job Title", "Shift_Code_S", "Shift_Code_O", "Shift_Code_D", "Shift_Code_S_Price", "Shift_Code_O_Price", "Shift_Code_D_Price"}),
    // 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(#"Reordered Columns", "CommonCode", each FindCommonPrefix([Shift_Code_S], [Shift_Code_O], [Shift_Code_D]), type text),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added CommonCode", {"Vendor", "Location", "Job Title", "Shift_Code_S", "Shift_Code_O", "Shift_Code_D", "CommonCode"}, "Attribute", "Value"),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Unpivoted Columns", "Shift Type", each Text.BetweenDelimiters([Attribute], "_", "_", 1, 0), type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Text Between Delimiters",{"Attribute"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns1",{"Vendor", "Location", "Job Title", "Shift_Code_S", "Shift_Code_O", "Shift_Code_D", "Shift Type", "Value"}),
    #"Added Conditional Column" = Table.AddColumn(#"Reordered Columns1", "Original Shift Code", each if [Shift Type] = "S" then [Shift_Code_S] else if [Shift Type] = "O" then [Shift_Code_O] else if [Shift Type] = "D" then [Shift_Code_D] else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Original Shift Code", type text}}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Changed Type1",{"Vendor", "Location", "Job Title", "Shift_Code_S", "Shift_Code_O", "Shift_Code_D", "Original Shift Code", "CommonCode", "Shift Type", "Value"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns2",{"Shift_Code_S", "Shift_Code_O", "Shift_Code_D"}),
    #"Added Shift Type N" = Table.AddColumn(#"Removed Columns2", "Shift Type N", each let 
        original = [Original Shift Code], 
        common = [CommonCode], 
        shiftType = [Shift Type],
        originalLength = Text.Length(original), 
        commonLength = Text.Length(common), 
        lengthDifference = originalLength - commonLength, 
        uncommon = if original = common then shiftType else if lengthDifference > 0 then Text.End(original, lengthDifference) else "" 
        in uncommon),
    #"Renamed Columns" = Table.RenameColumns(#"Added Shift Type N",{{"Value", "Price"}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns",{"Shift Type"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns3",{{"Shift Type N", "Shift Type"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Shift Type", type text}})
in
    #"Changed Type2"

[work_1111]

Hello @amustafa I have blank row added to my table and I can't figure out where it has come from. It says there are 102 errors with values that are null. I do have a few values in the shift code that are all numbers, no letters and the column is formated for text. Do you think that could be the problem? 

I get the error on all of the columns when I try to sort. But all of the columns say there is no error

[amustafa]

See my updated pbix file in my shared drive. Here's the updated M code. You need to unpivot columns and then create a conditional column to merge shift codes based on type. 

let
    Source = Excel.Workbook(File.Contents("C:\Users\aliom\OneDrive\Power BI Samples\Extract Commom String\Sample.xlsx"), null, true),
    Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"Job Title", type text}, {"Shift_Code_S", type text}, {"Shift_Code_S_Price", type number}, {"Shift_Code_O", type text}, {"Shift_Code_O_Price", type number}, {"Shift_Code_D", type text}, {"Shift_Code_D_Price", type number}, {"Raw_Shift Code (Desired new column)", type text}, {"Location", type text}, {"Vendor", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Raw_Shift Code (Desired new column)"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Vendor", "Location", "Job Title", "Shift_Code_S", "Shift_Code_O", "Shift_Code_D", "Shift_Code_S_Price", "Shift_Code_O_Price", "Shift_Code_D_Price"}),
    // 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),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added CommonCode", {"Vendor", "Location", "Job Title", "Shift_Code_S", "Shift_Code_O", "Shift_Code_D", "CommonCode"}, "Attribute", "Value"),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Unpivoted Columns", "Shift Type", each Text.BetweenDelimiters([Attribute], "_", "_", 1, 0), type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Text Between Delimiters",{"Attribute"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns1",{"Vendor", "Location", "Job Title", "Shift_Code_S", "Shift_Code_O", "Shift_Code_D", "Shift Type", "Value"}),
    #"Added Conditional Column" = Table.AddColumn(#"Reordered Columns1", "Original Shift Code", each if [Shift Type] = "S" then [Shift_Code_S] else if [Shift Type] = "O" then [Shift_Code_O] else if [Shift Type] = "D" then [Shift_Code_D] else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Original Shift Code", type text}}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Changed Type1",{"Vendor", "Location", "Job Title", "Shift_Code_S", "Shift_Code_O", "Shift_Code_D", "Original Shift Code", "CommonCode", "Shift Type", "Value"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns2",{"Shift_Code_S", "Shift_Code_O", "Shift_Code_D"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Value", "Price"}})
in
    #"Renamed Columns"

[work_1111]

@amustafa Thanks for your response. For the step "Inserted Text Between Delimiter", what I actually need to do is split apart the Shift_Code_S, "Shift_Code_O", "Shift_Code_D" columns based on the value that is in the common code field. So the values in the Shift type column should be all of the other data minus the common code. 

Job Title	Original_Shift_Code	Raw_Shift_Code	Shift_Type	Price	Location	Vendor
Janitor	FOHG24AFTLS	FOHG24AFTL	S	12.65	San Francisco	Company C
Janitor	FOHG24AFTLH	FOHG24AFTL	H	23.65	San Francisco	Company C
Janitor	FOHG24AFTLP	FOHG24AFTL	P	34.65	San Francisco	Company C
Janitor	FOHG24AFTLSN	FOHG24AFTL	SN	13.22	San Francisco	Company C
Janitor	FOHG24AFTLHN	FOHG24AFTL	HN	24.22	San Francisco	Company C
Janitor	FOHG24AFTLPN	FOHG24AFTL	PN	35.22	San Francisco	Company C

[amustafa · Accepted solution]

Hi @work_1111 ,

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/

[work_1111]

@amustafa Hoping you can help again. I have received additional requirements which I built out in separate queries but the queries are not merging properly. Ultimately what I need to do after adding the custom column for Common Code is split shift code column based on the value in the Raw_Shift Code amd then add rows for each item instead of having in columns. Please see current table and desired tables below. 

Current Table 

Job Title	Shift_Code_S	Shift_Code_S_Price	Shift_Code_O	Shift_Code_O_Price	Shift_Code_D	Shift_Code_D_Price	Raw_Shift Code (Desired new column)	Location	Vendor
Waiter	LCFNAPS	15.57	LCFNAPO	22.28	LCFNAPD	32.99	LCFNAP	Dallas	Company A
Server	8147R	15.81	8147RO	22.62	8147RP	33.44	8147R	Houston	Company B
Janitor	FOHG24AFTLS	12.65	FOHG24AFTLH	23.65	FOHG24AFTLP	34.65	FOHG24AFTL	San Francisco	Company C
Janitor	FOHG24AFTLSN	13.22	FOHG24AFTLHN	24.22	FOHG24AFTLPN	35.22	FOHG24AFTL	San Francisco	Company C

Desired Table

Job Title	Original_Shift_Code	Raw_Shift_Code (new)	Shift_Type (new)	Price	Location	Vendor
Waiter	LCFNAPS	LCFNAP	S	15.57	Dallas	Company A
Waiter	LCFNAPO	LCFNAP	O	22.28	Dallas	Company A
Waiter	LCFNAPD	LCFNAP	D	32.99	Dallas	Company A
Server	8147RS	8147R	S	15.81	Houston	Company B
Server	8147RO	8147R	O	22.62	Houston	Company B
Server	8147RP	8147R	P	33.44	Houston	Company B
Janitor	FOHG24AFTLS	FOHG24AFTL	S	12.65	San Francisco	Company C
Janitor	FOHG24AFTLH	FOHG24AFTL	H	23.65	San Francisco	Company C
Janitor	FOHG24AFTLP	FOHG24AFTL	P	34.65	San Francisco	Company C
Janitor	FOHG24AFTLSN	FOHG24AFTL	SN	13.22	San Francisco	Company C
Janitor	FOHG24AFTLHN	FOHG24AFTL	HN	24.22	San Francisco	Company C
Janitor	FOHG24AFTLPN	FOHG24AFTL	PN	35.22	San Francisco	Company C

[work_1111]

@amustafa Thank you! I've been working on this for the past few days, I should have posted sooner, your solution worked great! Thanks so much!

[BIswajit_Das]

Hello @work_1111 Just want to confirm one thing 
for each job title is there any specific code format like it's length or any specific starting letters.
can you provide data of 3 rows per job tiles
For example
Job         code

Waiter     LCFNAPS
Waiter     anothe code
Waiter     anothe code
Like this 
Thanks & Regards..

[work_1111]

@BIswajit_Das No there is not any specific code format, they are submitted from different companies, sometimes they will be the first 2 letters of the job title, but sometimes the codes do not any rhyme or reason. some other codes for Waiter are:

Waiter     NPACPS

Waiter     M03S

Waiter    CO4FW3ESLR

[BIswajit_Das]

Then as per your issue the last letter is the issue here and it may be 1 letter or multiple !!