Create Incremental Count column based on a Condition in PowerQuery

Question

Hello,&nbsp;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.&nbsp;Again, I need to do this in PowerQuery.&nbsp;&nbsp;(Not perfect) pseudocode would be something like this:&nbsp;entry_num = 1for row in data:&nbsp; &nbsp; if data[row][col1] == "ABC Company" or data[row][col1] == "ABC Co":&nbsp; &nbsp; &nbsp; &nbsp; data[row][col4] = entry_num&nbsp; &nbsp; &nbsp; &nbsp; entry_num = entry_num + 1&nbsp;TextDate,Order Type,IndexABC Company&nbsp;&nbsp;1Random text&nbsp;&nbsp;&nbsp;Random text&nbsp;&nbsp;&nbsp;ABC Co&nbsp;&nbsp;2Random text&nbsp;&nbsp;&nbsp;Random text&nbsp;&nbsp;&nbsp;ABC Company&nbsp;&nbsp;3Random text&nbsp;&nbsp;&nbsp;Random text&nbsp;&nbsp;&nbsp;ABC Co&nbsp;&nbsp;4Random text&nbsp;&nbsp;&nbsp;Random text&nbsp;&nbsp;&nbsp;

alexisolson · Accepted Answer

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"
&nbsp;
Result:

Forum Discussion

Create Incremental Count column based on a Condition in PowerQuery

1 Reply

Related content

Increment Refresh

Incremental refresh

PowerQuery transformation

Incremental Refresh

Delete rows with a condition in PowerQuery

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