Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Hiveman
Regular Visitor

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!

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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

View solution in original post

4 REPLIES 4
Hiveman
Regular Visitor

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"

 

lbendlin
Super User
Super User

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.