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

Question

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.

Lots of examples of countif() and countifs() on here, this is but one: stackoverflow.com/q/33358534/4961700 — Solar Mike
– Solar Mike, Commented Aug 19, 2022 at 7:38
i know how to use countif as an equation, but i need to use it to calculate something in power query, i can't find a way to calculate what i want in custom column in power query. — Mahmoud Badr
– Mahmoud Badr, Commented Aug 19, 2022 at 7:43
So will powerquery open a specific file? make a template then get powerquery to use it. — Solar Mike
– Solar Mike, Commented Aug 19, 2022 at 7:46
yes, i extract specific data from a master excel sheet then i want to count the occurrences of the column SF ID — Mahmoud Badr
– Mahmoud Badr, Commented Aug 19, 2022 at 7:51

Umut K · Accepted Answer · 2022-08-19 08:15:10Z

3

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"

answered Aug 19, 2022 at 8:15

Umut K

1,38813 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mahmoud Badr Over a year ago

many thanks, it's working but there a small issue, the rows repeated many times , could you advice why this happen

Umut K Over a year ago

@MahmoudBadr this is the result as you wanted expected result picture in your question. If you need something else, revise your question.

Mik · Accepted Answer · 2022-08-19 07:51:45Z

0

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

answered Aug 19, 2022 at 7:51

Mik

2,1011 gold badge7 silver badges21 bronze badges

5 Comments

Mahmoud Badr Over a year ago

i used this command but i found this error message (Expression.Error: The name 'CALCULATE' wasn't recognized. Make sure it's spelled correctly.)

Timeless Over a year ago

This is a DAX command, you can't use it in Power Query.

Mahmoud Badr Over a year ago

is there any way to calculate what i want i power query?

Mik Over a year ago

I'm not familiar with M. It was a DAX tag, so I added. Why don't you want to add calculated column to the table in the model? Seems it would be easer and fast enough.

Mik Over a year ago

@Tyler2P, thank you for the comment. I cant get the point what is the reason for M (may be it is) for the case. The measure itself and approach to solve the problem are very plain and simple, I don't see what to comment ).

Timeless · Accepted Answer · 2022-08-19 08:04:45Z

0

If I understood correctly, try this :

Select the two columns Date and SFID an make a groupby.

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.

edited Aug 19, 2022 at 8:04

answered Aug 19, 2022 at 7:51

Timeless

38.3k6 gold badges33 silver badges54 bronze badges

2 Comments

Mahmoud Badr Over a year ago

i used this option but the data count as the above screen shoot, i've eddied my question to see the issue

Timeless Over a year ago

So this is not a countif issue ! Take a look at the EDIT I added to my answer. If this is still not the result you're looking for, you may need to give a sample data other than a simple screenshot.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

5 Comments

EDIT :

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

5 Comments

EDIT :

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related