Forum Discussion
combining rows conditionally
I have a text file with a large number of rows containing a process id and result info, e.g. PID:1234 starttime: 2023-0101 15:21:05.003 merge completed PID: 5678 starttime: 2023-0102 18:03:05.00...
let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "fc5NC8IwDAbgvxJ2Vkna9cOevXjzXnYoLsqgpaOr4M+XFcFd5jEh7/PG++52vTgSsu+Gg++WGkqtU2IHAoU8IiEBKSfIoTohynaVuDwZ7jnNkSuPbbcyoLSxe44Asg7l1okcFgZ+z1P5KmtkhPyqP/NsjW7TI9QQgUvJZdPYa7PTSAiEjv5/PnwA", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t] ), #"Changed Type" = Table.TransformColumnTypes(Source, {{"Column1", type text}}), #"Added Custom" = Table.AddColumn( #"Changed Type", "PID", each if Text.StartsWith([Column1], "PID:") then Text.Replace([Column1], "PID:", "") else null ), #"Filled Down" = Table.FillDown(#"Added Custom", {"PID"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1] = "fatal error")) in #"Filtered Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"fc5NC8IwDAbgvxJ2Vkna9cOevXjzXnYoLsqgpaOr4M+XFcFd5jEh7/PG++52vTgSsu+Gg++WGkqtU2IHAoU8IiEBKSfIoTohynaVuDwZ7jnNkSuPbbcyoLSxe44Asg7l1okcFgZ+z1P5KmtkhPyqP/NsjW7TI9QQgUvJZdPYa7PTSAiEjv5/PnwA",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Column1 = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"PID",
each if Text.StartsWith([Column1], "PID:") then Text.Replace([Column1], "PID:", "") else null
),
#"Filled Down" = Table.FillDown(#"Added Custom", {"PID"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1] = "fatal error"))
in
#"Filtered Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Thanks, this looks like it will do the job. I just need to figure out how to convert the source block to work when the source is Source = Csv.Document(File.Contents and is followed by a number of replace and filter lines ending in #"Replaced Value3"
Power Query steps usually reference other steps. You would modify the "Added Custom" step to point to your #"Replaced Value3" step instead of #"Changed Type" .