Forum Discussion
replace value in column1 using value in column2
I am trying to remove the vlaue of column1 from column2. For example, column1 has the file extension of a file name in column2, I would like to remove the file extension from column2. So sort of a dynamic replace, using a column value as the search text and replacing it with blank.
Please always share a sample of your data to make it easier for those trying to answer
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0iupKFHSUcpLzE01BLNjdYCCqRWpUEEjMBssmJQIU2kMZsfGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Replace([Column2],[Column1],"")) in #"Added Custom"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
4 Replies
jsherman-q - I would just create a new column that does the necessary operation, remove the original column and then rename the new column to the old colum, like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0itIyqxQ0lHKz0uFMGN1gIJJiSVAsZLyfDALLFSRUwxSV5JRlJoK4cTGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Text Before Delimiter", each Text.BeforeDelimiter([Column2], "."), type text), #"Removed Columns" = Table.RemoveColumns(#"Inserted Text Before Delimiter",{"Column2"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Text Before Delimiter", "Column2"}}) in #"Renamed Columns"If i'm reading that right it looks like it just remvoed the text before the delimiter ".". If so, that's not really what i want. I want ot to remove the value in column1 from column2. I used the exmaple of a file extension but in some cases my data is not a file extension and does not have the ".".
Please always share a sample of your data to make it easier for those trying to answer
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0iupKFHSUcpLzE01BLNjdYCCqRWpUEEjMBssmJQIU2kMZsfGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Replace([Column2],[Column1],"")) in #"Added Custom"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
