Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

We've captured the moments from FabCon & SQLCon that everyone is talking about, and we are bringing them to the community, live and on-demand. Starts on April 14th. Register now

Reply
Syndicate_Admin
Administrator
Administrator

Cree una nueva columna con una cadena de otra columna si coincide con una cadena determinada

‎11-27-2023 12:29 PM

Tengo la columna A que contiene un texto así:

Fila 1:

request=END, method=POST, httpStatusCode=200, requestElapsedTime=52,

Fila 2:

request=BEGIN, method=POST, uri=/, hostname=local.host

Fila 3:

message='Tarea de inicio, httpStatusCode=404, hostname=local.host

etc...

Quiero crear una nueva columna, la columna B, que busque en la columna A el texto httpStatusCode y, si la encuentra, tome todo el texto a ambos lados de los delimitadores, una coma en este caso.

Los resultados deben ser:

Columna B

Fila 1:

httpStatusCode=200

Fila 2:

Fila 3:

httpStatusCode=404

¿Qué fórmula sería la mejor para que esto suceda?

Message 1 of 8
440 Views
0
7 REPLIES 7
Syndicate_Admin
Administrator
Administrator

‎11-29-2023 11:35 PM

@AK637 ,

si intenta reemplazar los valores nulos en [CONTENT.2.2.2.2.2] antes de agregar la columna B, ¿ayuda esto? 🙂

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

Message 8 of 8
400 Views
0
Syndicate_Admin
Administrator
Administrator

‎11-29-2023 01:30 PM

Gracias por la respuesta. Esto pareció funcionar 🙂

Sin embargo, un seguimiento rápido; Recibo el siguiente error cuando intento aplicar estos cambios:

error.png

He reemplazado los valores nulos en ColumnB, pero el problema persiste.

(También intenté reemplazar valores nulos en todas las columnas).

¿Alguna idea de a qué podría relacionarse el problema?

Message 7 of 8
407 Views
0
Syndicate_Admin
Administrator
Administrator

‎11-29-2023 10:22 AM

Hola @ak637 ,

Siguiente intento 🙂

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"

No olvides marcar la respuesta, en caso de que esto sea lo que necesitabas 🙂

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

Message 6 of 8
409 Views
0
Syndicate_Admin
Administrator
Administrator

‎11-29-2023 08:52 AM

Gracias por responderme, @tackytechtom.

Este código funcionó de alguna manera: parecía filtrar la columna CONTENT.2.2.2.2.2 para mostrar solo las filas que contienen httpStatusCode. La columna B que se creó parecía hacer lo que quería en el sentido de que solo mostraba statusHttpCode=200 , etc. ¿Es posible no alterar la columna CONTENT.2.2.2.2.2?

He escrito datos ficticios de lo que estoy usando como ejemplo, ¿esto podría ayudarte?

Ingrese lo siguiente en un archivo .txt y cárguelo en PBI y debería poder comprender con qué estoy trabajando 🙂

2023-09-06 09:42:46,322 INFO [sec-1] [CommandManagerResource] [task-001] (415x) 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) 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) 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) operation=filter, request= END, method=POST, uri=/sua, httpStatusCode=200, requestElapsedTime=52, ElapsedTime=31, hostname=localhost

¡Realmente aprecio tu tiempo!

Message 5 of 8
409 Views
0
Syndicate_Admin
Administrator
Administrator

‎11-28-2023 10:57 AM

Hola @ak637 ,

Esta es una pregunta difícil sin tener acceso a los datos reales, pero aquí mi intento:

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"

Hazme saber si esto ayuda 🙂

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

Message 4 of 8
416 Views
0
Syndicate_Admin
Administrator
Administrator

‎11-28-2023 07:32 AM

Gracias por la respuesta, @tackytechtom

Aquí está el código que ya tengo y estoy luchando por incorporar su código con él. ¿Podrías intentarlo?

Tenga en cuenta que la columna A es Contenido.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"

Para reiterar la pregunta en este nuevo contexto, todavía quiero crear una nueva columna (Columna B) que verifique la columna Content.2.2.2.2.2 para el texto httpStatusCode y, si se encuentra, toma todo el texto a ambos lados de los delimitadores, una coma en este caso.

Message 3 of 8
416 Views
0
Syndicate_Admin
Administrator
Administrator

‎11-27-2023 12:52 PM

@ak637 ,

Aquí una solución en Power Query:

tackytechtom_0-1701118101644.png


Simplemente pegue el siguiente código en el editor avanzado. Puedes consultar los pasos en el lado derecho:

tackytechtom_1-1701118211600.png

Aquí el código M:

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"

Hazme saber si esto ayuda 🙂

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

Message 2 of 8
425 Views
0

Helpful resources

Announcements
New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

Join our Fabric User Panel

Join our Fabric User Panel

Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.

March Power BI Update Carousel

Power BI Community Update - March 2026

Check out the March 2026 Power BI update to learn about new features.

View All
Top Kudoed Authors
View All