Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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.003
lease expired
timed out
PID: 9876
fatal error
PID: 5467
starttime: 2023-0110 10:11:05.003
merge completed
etc, etc
I want to narrow this list down to only the process ids that had a fatal error, but the id's and the result are on separate separate lines. I can filter for just the PID lines and the fatal error lines, but that leaves me with:
PID:1234
PID: 5678
PID: 9876
fatal error
PID: 5467
Is there a way to filter based on the subsequent row value? My ideal goal would be to get to:
PID | Result |
9876 | fatal error |
Appreciate any suggestions!
Solved! Go to Solution.
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 @lbendlin, you got me on right track. Here's the final code:
let
//My log.txt file had no tabs so this was just an easy way to keep the text in one block
Source = Csv.Document(File.Contents("C"\Work\log.txt"),[Delimiter="#(tab)", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
//Eliminate the rows I don't need:
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.Contains([Column1], "PID:") or Text.Contains([Column1], "fatal error")),
//Add the new column 2 called Results and populate it with the error message where the error message is in column 1:
#"Added Custom" = Table.AddColumn(
#"Filtered Rows",
"Result",
each if Text.StartsWith([Column1], "fatal") then Text.Replace([Column1], "fatal error", "Fatal Error") else null
),
//Get rid of the original error message in column 1 leaving a null value
#"Replaced Value" = Table.ReplaceValue(#"Added Custom","fatal error",null,Replacer.ReplaceValue,{"Column1"}),
// Rename column 1 PID:
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Column1", "PID"}}),
//Find all the null values in the PID column and replace them with the value in the row above
#"Filled Down" = Table.FillDown(#"Renamed Columns", {"PID"}),
//Eliminate all the rows where the Results column is null
#"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Error Message] = "fatal error")),
in
#"Filtered Rows1"
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" .