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 ...
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/
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
Most Valuable Professional
Most Valuable ProfessionalHi 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/
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/