Forum Discussion
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/
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 |
| 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 |