Create Incremental Count column based on a Condition in PowerQuery

[cferv_77]

Hello,

I have this data in PDF and after parsing it, I am trying to create a conditional column in PowerQuery to use a "counter". After the line with the company name lies the data I need. Below is a simple example. I cannot use filter because I need to concat some text between each new entry. So the new "counter" column is needed to help in the text grouping. Table example below.

Again, I need to do this in PowerQuery. 

(Not perfect) pseudocode would be something like this: 

entry_num = 1
for row in data:
    if data[row][col1] == "ABC Company" or data[row][col1] == "ABC Co":
        data[row][col4] = entry_num
        entry_num = entry_num + 1

Text	Date,	Order Type,	Index
ABC Company			1
Random text
Random text
ABC Co			2
Random text
Random text
ABC Company			3
Random text
Random text
ABC Co			4
Random text
Random text

[AlexisOlson · Accepted solution]

You could do this with List.Generate or List.Accumulate but I think it's easier to add an index, filter for ABC, add a new index on the filtered subtable, and then merge the subtable with the full unfiltered step like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVnDOzy1IzKtUitWJVgpKzEvJz1UoSa0owcqHqCdBKfWNjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Added Index1" = Table.AddIndexColumn(Source, "FullIndex", 1, 1, Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Added Index1", each ([Text] = "ABC Company" or [Text] = "ABC Co")),
    #"Added Index2" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"FullIndex"}, #"Added Index2", {"FullIndex"}, "Added Index", JoinKind.LeftOuter),
    #"Expanded Added Index" = Table.ExpandTableColumn(#"Merged Queries", "Added Index", {"Index"}, {"Index"})
in
    #"Expanded Added Index"

Result: