Solved: Re: Create new column with string from another col...

ak637 · ‎11-27-2023

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 · ‎11-29-2023

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/

Did I answer your question❓➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn!

#proudtobeasuperuser

View solution in original post

tackytechtom · ‎11-27-2023

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/

Did I answer your question❓➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn!

#proudtobeasuperuser

ak637 · ‎11-28-2023

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 · ‎11-28-2023

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/

Did I answer your question❓➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn!

#proudtobeasuperuser

ak637 · ‎11-29-2023

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 · ‎11-29-2023

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/

Did I answer your question❓➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn!

#proudtobeasuperuser

ak637 · ‎11-29-2023

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 · ‎11-29-2023

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/

Did I answer your question❓➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn!

#proudtobeasuperuser

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

Helpful resources

New forum boards available in Real-Time Intelligence.

Fabric certifications survey

How to Get Your Question Answered Quickly

Jumpstart your career with the Fabric Career Hub

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

Helpful resources

New forum boards available in Real-Time Intelligence.

Fabric certifications survey

How to Get Your Question Answered Quickly