Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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.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!

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago
    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".

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago
    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".

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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"

       

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        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" .

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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"