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 TitleShift_Code_SShift_Code_OShift_Code_DRaw_Shift Code (Desired Result)WaiterLCFNAPSLCFNAPOLCFNAPDLCFNAPServer8147R8147RO8147RP8147RJanitorFOHG24AFTLSFOHG24AFTLHFOHG24AFTLPFOHG24AFTLJanitorFOHG24AFTLSNFOHG24AFTLHNFOHG24AFTLPNFOHG24AFTL

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

12 Replies

Solution Sage

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/

Anonymous
Not applicable
2 years ago
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!

Anonymous

Not applicable

2 years ago

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

amustafa

Solution Sage

2 years ago

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"

Anonymous
Not applicable
2 years ago
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

BIswajit_Das
Impactful Individual
2 years ago
Hello Anonymous 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..
- Anonymous
  Not applicable
  2 years ago
  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
    Impactful Individual
    2 years ago
    Then as per your issue the last letter is the issue here and it may be 1 letter or multiple !!

amustafa

Solution Sage

2 years ago

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"

Anonymous

Not applicable

2 years ago

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

Anonymous
Not applicable
2 years ago
amustafa THANK YOU!
amustafa
Solution Sage
2 years ago
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.

Forum Discussion

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

12 Replies

Related content

Lazy evaluation

Dataflow evaluation error

1. How to add two different calculated columns of two different table

Powerbi Desktop stuck 'evaluating'

Expression Evaluate , error