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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors