Forum Discussion
Solved
Create new column with string from another column if it matches a given string
I have Column A which contains text like so:
Row 1:
| request=END, method=POST, httpStatusCode=200, requestElapsedTime=52, |
Row 2:
| request=BEGIN, method=POST, uri=/, hostname=local.host |
Row3:
| message='Starting task, httpStatusCode=404, hostname=local.host |
etc...
I want to create a new column, Column B, that searches Column A for the text httpStatusCode, and if found, takes all text either side of the delimiters, a comma in this case.
The results should be:
Column B
Row 1:
httpStatusCode=200 |
Row 2:
Row 3:
| httpStatusCode=404 |
What formula would be best to make this happen?
Hi ak637 ,
Next try 🙂
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZFPa8JAEMW/yuCphQ1OdqOisIdiFTwYRb2lOSzJtoYm2XT/QKH0u3djWi3SlnoqPe28mZ3H/HhJ0qNIWYDjAIeA40lEJ9GQMEphEc9XkBiZBWEKyVRVlajzpajFg9QbaZTTmfQDK8xjgOj/XEXh4Pka7hwiy+q+OBQSVCO1sIWqea1usrYgsBZaVNJKbXisTuJlqXJZ8naRjkJkIXYlgSKPXfU+wGO3s1vkH4PueSWwV8bW3pSXKhNlq3op+QaVDc5QN/LJSWPnRelvOhFST0jZ6EfC+8MOAd1Z8Fl8S8Cz7VXO16vtjoDTBe8bJ/yN1jZbK6wzU0/NKeJxb1aKxsh8V3iCASXwWTN2Id7wwiTZv00ywt8lGbVJ0r9PMvwaL30D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None), {"Column1", "Column2"}), #"Inserted After Delimiter" = Table.AddColumn(#"Split Column by Delimiter", "Column3", each Text.AfterDelimiter([Column2], ":", 1), type text), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Inserted After Delimiter", {{"Column2", each Text.BeforeDelimiter(_, ":", 1), type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Extracted Text Before Delimiter", "Column3", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None), {"Column3", "Column4"}), #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Column4", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None), {"Column4", "Column5"}), #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "Column5", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None), {"Column5", "Column6"}), #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter3", {{"Column1", "DATE"}, {"Column2", "TIME"}, {"Column3", "MILLISECONDS"}, {"Column4", "TYPE"}, {"Column5", "[]"}, {"Column6", "CONTENT"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"DATE", type date}, {"TIME", type time}, {"MILLISECONDS", Int64.Type}, {"TYPE", type text}, {"[]", type text}, {"CONTENT", type text}}), #"Split Column by Delimiter4" = Table.SplitColumn(#"Changed Type", "CONTENT", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"CONTENT.1", "CONTENT.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"CONTENT.1", type text}, {"CONTENT.2", type text}}), #"Split Column by Delimiter5" = Table.SplitColumn(#"Changed Type1", "CONTENT.2", Splitter.SplitTextByEachDelimiter({"] "}, QuoteStyle.Csv, false), {"CONTENT.2.1", "CONTENT.2.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter5",{{"CONTENT.2.1", type text}, {"CONTENT.2.2", type text}}), #"Split Column by Delimiter6" = Table.SplitColumn(#"Changed Type2", "CONTENT.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"CONTENT.2.2.1", "CONTENT.2.2.2"}), #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter6",{{"CONTENT.2.2.1", type text}, {"CONTENT.2.2.2", type text}}), #"Split Column by Delimiter7" = Table.SplitColumn(#"Changed Type3", "CONTENT.2.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"CONTENT.2.2.2.1", "CONTENT.2.2.2.2"}), #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter7",{{"CONTENT.2.2.2.1", type text}, {"CONTENT.2.2.2.2", type text}}), #"Split Column by Delimiter8" = Table.SplitColumn(#"Changed Type4", "CONTENT.2.2.2.2", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"CONTENT.2.2.2.2.1", "CONTENT.2.2.2.2.2"}), #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter8",{{"CONTENT.2.2.2.2.1", type text}, {"CONTENT.2.2.2.2.2", type text}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type5",{{"CONTENT.2.2.2.2.1", "OPERATION"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Custom", each Text.Contains([CONTENT.2.2.2.2.2], "httpStatusCode=200")), #"Add ColumnB" = Table.AddColumn(#"Added Custom", "ColumnB", each Text.Split([CONTENT.2.2.2.2.2], ", ")), #"Expanded Custom" = Table.ExpandListColumn(#"Add ColumnB", "ColumnB"), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "CustomB2", each if Text.Contains([ColumnB], "httpStatusCode=") then [ColumnB] else null), #"Replace Values" = Table.ReplaceValue(#"Expanded Custom", each [ColumnB], each if Text.Contains([ColumnB], "httpStatusCode=") then [ColumnB] else null, Replacer.ReplaceValue,{"ColumnB"}), #"Filtered Rows" = Table.SelectRows(#"Replace Values", each [Custom] = true and [ColumnB] <> null or [Custom] = false), #"Removed Duplicates" = Table.Distinct(#"Filtered Rows") in #"Removed Duplicates"Don't forget to mark the answer, in case this is what you needed 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
7 Replies
- tackytechtom
Most Valuable Professional
Hi ak637 ,
Here a solution in Power Query:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY7BCsIwEER/JfQcNISKp1zUIF6q0N5qD8EsbWjT1Ozm/40iCIrHgXlvpm2LCPcESEpXB8480BCsupzrhrOBaKnJUMJ9sKCkEJy923oyC4JtnAe1kZwVHf+Ydvp4qr5cKTq1zsqANJsMTeFmptUzvlAPiKYHdU1CyG0ejeTmnpHB8edHKco/ou4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [columnA = _t]), ColumnB = Table.AddColumn(Source, "ColumnB", each Text.Split([columnA], ", ")), #"Expanded Custom" = Table.ExpandListColumn(ColumnB, "ColumnB"), #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each Text.Contains([ColumnB], "httpStatusCode")) in #"Filtered Rows"Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Thanks for the reply, tackytechtom
Here's the code I already have and I'm struggling to incorporate your code with it. Could you give it a go?
Please note that Column A is Content.2.2.2.2.2
let Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:....log.txt"), null, null, 1252)}), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None), {"Column1", "Column2"}), #"Inserted After Delimiter" = Table.AddColumn(#"Split Column by Delimiter", "Column3", each Text.AfterDelimiter([Column2], ":", 1), type text), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Inserted After Delimiter", {{"Column2", each Text.BeforeDelimiter(_, ":", 1), type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Extracted Text Before Delimiter", "Column3", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None), {"Column3", "Column4"}), #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Column4", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None), {"Column4", "Column5"}), #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "Column5", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None), {"Column5", "Column6"}), #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter3", {{"Column1", "DATE"}, {"Column2", "TIME"}, {"Column3", "MILLISECONDS"}, {"Column4", "TYPE"}, {"Column5", "[]"}, {"Column6", "CONTENT"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"DATE", type date}, {"TIME", type time}, {"MILLISECONDS", Int64.Type}, {"TYPE", type text}, {"[]", type text}, {"CONTENT", type text}}), #"Split Column by Delimiter4" = Table.SplitColumn(#"Changed Type", "CONTENT", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"CONTENT.1", "CONTENT.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"CONTENT.1", type text}, {"CONTENT.2", type text}}), #"Split Column by Delimiter5" = Table.SplitColumn(#"Changed Type1", "CONTENT.2", Splitter.SplitTextByEachDelimiter({"] "}, QuoteStyle.Csv, false), {"CONTENT.2.1", "CONTENT.2.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter5",{{"CONTENT.2.1", type text}, {"CONTENT.2.2", type text}}), #"Split Column by Delimiter6" = Table.SplitColumn(#"Changed Type2", "CONTENT.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"CONTENT.2.2.1", "CONTENT.2.2.2"}), #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter6",{{"CONTENT.2.2.1", type text}, {"CONTENT.2.2.2", type text}}), #"Split Column by Delimiter7" = Table.SplitColumn(#"Changed Type3", "CONTENT.2.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"CONTENT.2.2.2.1", "CONTENT.2.2.2.2"}), #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter7",{{"CONTENT.2.2.2.1", type text}, {"CONTENT.2.2.2.2", type text}}), #"Split Column by Delimiter8" = Table.SplitColumn(#"Changed Type4", "CONTENT.2.2.2.2", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"CONTENT.2.2.2.2.1", "CONTENT.2.2.2.2.2"}), #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter8",{{"CONTENT.2.2.2.2.1", type text}, {"CONTENT.2.2.2.2.2", type text}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type5",{{"CONTENT.2.2.2.2.1", "OPERATION"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Custom", each Text.Contains([CONTENT.2.2.2.2.2], "httpStatusCode=200")) in #"Added Custom"To reiterate the question in this new context, I still want to create a new column (Column B) that checks column Content.2.2.2.2.2 for the text httpStatusCode, and if found, takes all text either side of the delimiters, a comma in this case.
- tackytechtom
Hi ak637 ,
This is a hard one without having access to the actual data, but here my try:
let Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:....log.txt"), null, null, 1252)}), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None), {"Column1", "Column2"}), #"Inserted After Delimiter" = Table.AddColumn(#"Split Column by Delimiter", "Column3", each Text.AfterDelimiter([Column2], ":", 1), type text), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Inserted After Delimiter", {{"Column2", each Text.BeforeDelimiter(_, ":", 1), type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Extracted Text Before Delimiter", "Column3", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None), {"Column3", "Column4"}), #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Column4", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None), {"Column4", "Column5"}), #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "Column5", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None), {"Column5", "Column6"}), #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter3", {{"Column1", "DATE"}, {"Column2", "TIME"}, {"Column3", "MILLISECONDS"}, {"Column4", "TYPE"}, {"Column5", "[]"}, {"Column6", "CONTENT"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"DATE", type date}, {"TIME", type time}, {"MILLISECONDS", Int64.Type}, {"TYPE", type text}, {"[]", type text}, {"CONTENT", type text}}), #"Split Column by Delimiter4" = Table.SplitColumn(#"Changed Type", "CONTENT", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"CONTENT.1", "CONTENT.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"CONTENT.1", type text}, {"CONTENT.2", type text}}), #"Split Column by Delimiter5" = Table.SplitColumn(#"Changed Type1", "CONTENT.2", Splitter.SplitTextByEachDelimiter({"] "}, QuoteStyle.Csv, false), {"CONTENT.2.1", "CONTENT.2.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter5",{{"CONTENT.2.1", type text}, {"CONTENT.2.2", type text}}), #"Split Column by Delimiter6" = Table.SplitColumn(#"Changed Type2", "CONTENT.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"CONTENT.2.2.1", "CONTENT.2.2.2"}), #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter6",{{"CONTENT.2.2.1", type text}, {"CONTENT.2.2.2", type text}}), #"Split Column by Delimiter7" = Table.SplitColumn(#"Changed Type3", "CONTENT.2.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"CONTENT.2.2.2.1", "CONTENT.2.2.2.2"}), #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter7",{{"CONTENT.2.2.2.1", type text}, {"CONTENT.2.2.2.2", type text}}), #"Split Column by Delimiter8" = Table.SplitColumn(#"Changed Type4", "CONTENT.2.2.2.2", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"CONTENT.2.2.2.2.1", "CONTENT.2.2.2.2.2"}), #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter8",{{"CONTENT.2.2.2.2.1", type text}, {"CONTENT.2.2.2.2.2", type text}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type5",{{"CONTENT.2.2.2.2.1", "OPERATION"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Custom", each Text.Contains([CONTENT.2.2.2.2.2], "httpStatusCode=200")), #"Add ColumnB" = Table.AddColumn(#"Added Custom", "ColumnB", each Text.Split([CONTENT.2.2.2.2.2], ", ")), #"Expanded Custom" = Table.ExpandListColumn(#"Add ColumnB", "ColumnB"), #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each Text.Contains([ColumnB], "httpStatusCode")) in #"Filtered Rows"Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
