Create new column with string from another column if it matches a given string

[ak637]

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?

[tackytechtom]

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/

[ak637]

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/

[ak637]

Thanks for getting back to me, @tackytechtom.

This code worked in someway - it seemed to filter the CONTENT.2.2.2.2.2 column to only show the rows that contain httpStatusCode. ColumnB that was created seemed to do what I wanted in that it displayed only statusHttpCode=200 etc. Is it possible to not the alter CONTENT.2.2.2.2.2 column?

I've written dummy data of what I'm using as an example - this might help you?

Enter the below into a .txt file and load into PBI and you should be able to get an understanding of what I'm working with 🙂

2023-09-06 09:42:46,322 INFO [sec-1] [CommandManagerResource] [task-001] (415x) <n/a> operation=noAction, Parameters=NoParameters{Model='10310', idNum='00', ActionId='0'}, hostname=localhost

2023-09-06 09:42:46,335 INFO [sec-1] [RequestFilter] [task-002] (237x) <n/a> operation=filter, request=END, method=POST, uri=/sua, httpStatusCode=200, requestElapsedTime=52, ElapsedTime=33, hostname=localhost

2023-09-06 09:42:46,336 INFO [sec-1] [CommandManagerResource] [task-003] (415x) <n/a> operation=noAction, Parameters=NoParameters{Model='10310', idNum='00', ActionId='0'}, hostname=localhost

2023-09-06 09:42:46,340 INFO [sec-1] [RequestFilter] [task-004] (227x) <n/a> operation=filter, request=END, method=POST, uri=/sua, httpStatusCode=200, requestElapsedTime=52, ElapsedTime=31, hostname=localhost

Really appreciate your time!

[tackytechtom · Accepted solution]

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/

[ak637]

Thanks for the reply. This seemed to work 🙂

However, a quick follow-up; I'm getting the following error when I try to apply these changes:

I've replaced the null values in ColumnB but the issue still persists.

(Also tried replacing null values in all columns).

Any idea what the issue could relate to?

****UPDATE:

I've identified the issue occurs after the second step:

#"Expanded Custom" = Table.ExpandListColumn(#"Add ColumnB", "ColumnB"),

[tackytechtom]

HI @ak637 ,

if you try to replace the nulls in [CONTENT.2.2.2.2.2] before ColumnB is added, does this help? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/