Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
work_1111
Helper II
Helper II

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

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)
WaiterLCFNAPSLCFNAPOLCFNAPDLCFNAP
Server8147R8147RO8147RP8147R
JanitorFOHG24AFTLSFOHG24AFTLHFOHG24AFTLPFOHG24AFTL
JanitorFOHG24AFTLSNFOHG24AFTLHNFOHG24AFTLPNFOHG24AFTL



1 ACCEPTED SOLUTION
amustafa
Solution Sage
Solution Sage

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/

View solution in original post

12 REPLIES 12
amustafa
Solution Sage
Solution Sage

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
Helper II
Helper II

@amustafa THANK YOU!

amustafa
Solution Sage
Solution Sage

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"

 

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? 

work_1111_0-1708064989220.png

 

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

work_1111_1-1708065091985.png

 

amustafa
Solution Sage
Solution Sage

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"

@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   
JanitorFOHG24AFTLSFOHG24AFTLS12.65San FranciscoCompany C
JanitorFOHG24AFTLHFOHG24AFTLH23.65San FranciscoCompany C
JanitorFOHG24AFTLPFOHG24AFTLP34.65San FranciscoCompany C
JanitorFOHG24AFTLSNFOHG24AFTLSN13.22San FranciscoCompany C
JanitorFOHG24AFTLHNFOHG24AFTLHN24.22San FranciscoCompany C
JanitorFOHG24AFTLPNFOHG24AFTLPN35.22San FranciscoCompany C
amustafa
Solution Sage
Solution Sage

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/

@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 TitleShift_Code_SShift_Code_S_PriceShift_Code_OShift_Code_O_PriceShift_Code_DShift_Code_D_PriceRaw_Shift Code (Desired new column)LocationVendor
WaiterLCFNAPS15.57LCFNAPO22.28LCFNAPD32.99LCFNAPDallasCompany A
Server8147R15.818147RO22.628147RP33.448147RHoustonCompany B
JanitorFOHG24AFTLS12.65FOHG24AFTLH23.65FOHG24AFTLP34.65FOHG24AFTLSan FranciscoCompany C
JanitorFOHG24AFTLSN13.22FOHG24AFTLHN24.22FOHG24AFTLPN35.22FOHG24AFTLSan FranciscoCompany C

 

Desired Table

Job Title   Original_Shift_Code   Raw_Shift_Code (new)   Shift_Type (new)   Price   Location   Vendor   
WaiterLCFNAPSLCFNAPS15.57DallasCompany A
WaiterLCFNAPOLCFNAPO22.28DallasCompany A
WaiterLCFNAPDLCFNAPD32.99DallasCompany A
Server8147RS8147RS15.81HoustonCompany B
Server8147RO8147RO22.62HoustonCompany B
Server8147RP8147RP33.44HoustonCompany B
JanitorFOHG24AFTLSFOHG24AFTLS12.65San FranciscoCompany C
JanitorFOHG24AFTLHFOHG24AFTLH23.65San FranciscoCompany C
JanitorFOHG24AFTLPFOHG24AFTLP34.65San FranciscoCompany C
JanitorFOHG24AFTLSNFOHG24AFTLSN13.22San FranciscoCompany C
JanitorFOHG24AFTLHNFOHG24AFTLHN24.22San FranciscoCompany C
JanitorFOHG24AFTLPNFOHG24AFTLPN35.22San FranciscoCompany C

@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
Resolver III
Resolver III

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..

@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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors