Retrieve value from previous or following row in PowerBI with PowerQueryM

Question

I'm trying to read in a log file to PowerBI with Power Query. I know there are better options for such an usecase, but I'm searching a solution to achieve this with Power Query or at least DAX!

What I want to accomplish is to write information (string) from a previous or following row (both possible) to another via an user defined column.

Sample Data

Index;LogText;ErrorCategorie
0;BatchA;0
1;BatchA;0
2;BatchB;0
3;ErrorZ;1
4;BatchB;0
5;BatchC;0
6;ErrorY;1
7;ErrorX;1
8;ErrorW;1
9;BatchC;0

As you can see there may be a random number of errors per Batch. I want to add the closest Batch in a new column to those errors, because the error occured while this batch was running.

ExpectedOutput:

Index;LogText;ErrorCategorie;Batch
0;BatchA;0;null
1;BatchA;0;null
2;BatchB;0;null
3;ErrorZ;1;BatchB
4;BatchB;0;null
5;BatchC;0;null
6;ErrorY;1;BatchC
7;ErrorX;1;BatchC
8;ErrorW;1;BatchC
9;BatchC;0;null

I hope you guys have a great idea. Does something like this theoretically work with PowerQuery M and let ... in definitions? Unfortunately I doesn't know much about it.

Answer 1

You can try this in powerquery

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "PriorBatch", each try if Text.Contains([Column1],"Error") then null else Text.Split([Column1],";"){1}  otherwise "zero"),
#"Filled Down" = Table.FillDown(#"Added Custom",{"PriorBatch"}),
#"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom", each if  [PriorBatch]=null then [Column1] else if Text.Contains([Column1],"Error") then [Column1]&";"&[PriorBatch] else [Column1]&";null"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"PriorBatch"})
in  #"Removed Columns"