Forum Discussion

cferv_77's avatar
cferv_77
Helper I
3 years ago
Solved

Create Incremental Count column based on a Condition in PowerQuery

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 ...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    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:

AlexisOlson's avatar
AlexisOlson
Super User
3 years ago

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: