how to count occurrences of values in a specific column in excel power query-CodePudding

I want to calculate the running count of each value based on column SF ID. In Excel power query , I am trying to apply countif in the following table but i cant find this equation here.

I would like to get the same result in excel Power query. Can you please advise.

i've used to group the date like below but this isn't the result that i want.

CodePudding user response：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcy5CQAwDASwXVwHzm eWUz2XyOQzuBWhTJJIFCWoEFizCx0R5JCi pXgxW1rw5vhkA0w8RshoXVDBu7GQ5OUad7Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"SF ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"SF ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SF ID"}, {{"Count", each _, type table [Date=nullable date, SF ID=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index", 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "SF ID", "Index"}, {"Date", "SF ID", "Index"})
in
    #"Expanded Custom"

CodePudding user response：

CALCULATE(
    COUNTROWS(tbl)
    ,ALLEXCEPT(tbl,tbl[SF ID])
    ,tbl[Date]<=MAX(tbl[Date])
)

CodePudding user response：

If I understood correctly, try this :

Select the two columns Date and SFID an make a

EDIT :

Open the Advanced Editor and put the code below :

 let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"SF ID", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"SF ID", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"SF ID"}, {{"AllData", each _, type table [Date=nullable datetime, SFID=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Status", each Table.AddIndexColumn([AllData], "Status", 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Status", {"Date", "Status"}, {"Date", "Status"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"AllData"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "SF ID", "Status"})
in
    #"Reordered Columns"

Make sure that your table is named "Table1". Otherwise, you have to rename it.