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 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?
I get the error on all of the columns when I try to sort. But all of the columns say there is no error