1

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.

enter image description here

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

enter image description here

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

enter image description here

Mahmoud Badr
  • 61
  • 2
  • 8
  • Lots of examples of countif() and countifs() on here, this is but one: https://stackoverflow.com/q/33358534/4961700 – Solar Mike Aug 19 '22 at 07: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 Aug 19 '22 at 07:43
  • So will powerquery open a specific file? make a template then get powerquery to use it. – Solar Mike Aug 19 '22 at 07: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 Aug 19 '22 at 07:51

3 Answers3

2
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"
Umut K
  • 1,364
  • 12
  • 25
  • many thanks, it's working but there a small issue, the rows repeated many times , could you advice why this happen – Mahmoud Badr Aug 19 '22 at 08:23
  • @MahmoudBadr this is the result as you wanted expected result picture in your question. If you need something else, revise your question. – Umut K Aug 19 '22 at 08:28
0

If I understood correctly, try this :

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

enter image description here enter image description here

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.

Timeless
  • 22,580
  • 4
  • 12
  • 30
  • i used this option but the data count as the above screen shoot, i've eddied my question to see the issue – Mahmoud Badr Aug 19 '22 at 07:58
  • 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. – Timeless Aug 19 '22 at 08:05
0
CALCULATE(
    COUNTROWS(tbl)
    ,ALLEXCEPT(tbl,tbl[SF ID])
    ,tbl[Date]<=MAX(tbl[Date])
)
Mik
  • 2,099
  • 1
  • 5
  • 19
  • i used this command but i found this error message (Expression.Error: The name 'CALCULATE' wasn't recognized. Make sure it's spelled correctly.) – Mahmoud Badr Aug 19 '22 at 07:58
  • This is a DAX command, you can't use it in Power Query. – Timeless Aug 19 '22 at 07:59
  • is there any way to calculate what i want i power query? – Mahmoud Badr Aug 19 '22 at 08:00
  • 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 Aug 19 '22 at 08:16
  • @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 ). – Mik Aug 19 '22 at 09:24