Forum Discussion

mushir1232's avatar
mushir1232
Regular Visitor
3 years ago

CountIf with Sequence Result

Hi All, Need Assistance in Power Query  to DAX Measure to get the Sequential Count result on Countif. I have applied : =COUNTIF($A$2:A2,A2)    
Need Help
AlienSx's avatar
AlienSx
Super User
3 years ago

Hi, mushir1232 you'd probably want to keep sorting order so I added index column before transformation and removed it in the end. 

let
    // Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source = <your_table>,
    sort_col = Table.AddIndexColumn(Source, "sort", 0, 1, Int64.Type),
    f = (tbl as table) as table => [a = Table.Sort(tbl, "sort"), b = Table.AddIndexColumn(a, "Result", 1, 1, Int64.Type)][b],
    g = Table.Group(sort_col, "Data", {{"all", each f(_)}}),
    all = Table.Combine(g[all]),
    sort_again = Table.Sort(all,{{"sort", Order.Ascending}}),
    remove_sort = Table.RemoveColumns(sort_again,{"sort"})
in
    remove_sort
  • mushir1232's avatar
    mushir1232
    Regular Visitor
    3 years ago

    Hi, thanks for your time to workout.

    What I Need a another Column in front of my Data showing the No of Counts . I want to know how many times the item is there in the Column with a sequence.

    If A is available in Different Row , in the given sample 4 times, so from the Top the It is counting and checking and providing the No of Counts from the Top always.

    Similar to COUNTIF($A$2:A2,A2) in Excel