Count Instance of ID Occurences According to Date

[cmaloyb]

I am trying to add a column in Power Query that will give the count of the amount of time an ID occurs and that number (count) based on date in chronological order. Here is an example of what I am looking for:

ID#	Date	Count
0001	1/1/2019	1
0001	2/6/2020	2
002	3/24/2018	1
002	6/7/2020	3
002	4/2/2019	2

The "Count" column is what I am looking to add. I would like to do this in Power Query if possible. 

[Mariusz · Accepted solution]

Hi @cmaloyb 

You can paste this code into Blank Query, the step you want to look at is Grouped Rows

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcrJDcAgEAPAXvxGstdBHLWg7b+NIJFH+I5mLUgKFFgxGQxk+ZlFs33m0wYful62W2O/22SlkfkC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID#" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID#"}, {{"Count", each Table.AddIndexColumn( Table.Sort( _, { "Date" } ), "Count", 1, 1 ) , type table [#"ID#"=nullable text, Date=nullable date, Count=nullable number]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date", "Count"}, {"Date", "Count"})
in
    #"Expanded Count"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[edhans · Accepted solution]

Hi @cmaloyb - see if this will help. It will return this ID Count column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUTLUN9Q3MjC0VIrVgYsZ6ZsBxYwMoGJGQCFjfSMTkDoLJDEzfXN0ZUA1UNNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID#" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"ID Count" = 
        Table.AddColumn(
            #"Changed Type",
            "ID Count",
            each
                let
                    varCurrentID = [#"ID#"],
                    varCurrentDate = [Date]
                in
            Table.RowCount(
                Table.SelectRows(#"Changed Type", each [#"ID#"] = varCurrentID and [Date] <= varCurrentDate)
            ),
            Int64.Type
        )
in
    #"ID Count"

 How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Anonymous · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RYzBCQAhDAR7yVvIZhXvrEXsvw1X0TvIIzsM07uFJUO4joimETbSwXRUYUKDG1NfdpYlv5+8qEw8V84/Lq7MSasxJg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ID#" = _t, Date = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID#", Int64.Type}, {"Date", type date}, {"Count", Int64.Type}},"en-US"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID#"}, {{"idx", (t)=>  Table.AddColumn(t,"rank",each  List.PositionOf(List.Sort(t[Date]),[Date])+1)}}),
    #"Expanded idx" = Table.ExpandTableColumn(#"Grouped Rows", "idx", {"Date", "Count", "rank"}, {"Date", "Count", "rank"})
in
    #"Expanded idx"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RYzBCQAhDAR7yVvIZhXvrEXsvw1X0TvIIzsM07uFJUO4joimETbSwXRUYUKDG1NfdpYlv5+8qEw8V84/Lq7MSasxJg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ID#" = _t, Date = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID#", Int64.Type}, {"Date", type date}, {"Count", Int64.Type}},"en-US"),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"ID#"}, {{"idx", each Table.AddIndexColumn(Table.Sort(_,{"Date"}),"cc",1,1)}}),
    #"Expanded idx" = Table.ExpandTableColumn(#"Grouped Rows", "idx", {"Date", "Count", "Index", "cc"}, {"Date", "Count", "Index", "cc"}),
    #"Sorted Rows" = Table.Sort(#"Expanded idx",{{"Index", Order.Ascending}})
in
    #"Sorted Rows"

[Mariusz · Accepted solution]

Hi @cmaloyb 

You can paste this code into Blank Query, the step you want to look at is Grouped Rows

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcrJDcAgEAPAXvxGstdBHLWg7b+NIJFH+I5mLUgKFFgxGQxk+ZlFs33m0wYful62W2O/22SlkfkC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID#" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID#"}, {{"Count", each Table.AddIndexColumn( Table.Sort( _, { "Date" } ), "Count", 1, 1 ) , type table [#"ID#"=nullable text, Date=nullable date, Count=nullable number]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date", "Count"}, {"Date", "Count"})
in
    #"Expanded Count"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[edhans · Accepted solution]

Hi @cmaloyb - see if this will help. It will return this ID Count column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUTLUN9Q3MjC0VIrVgYsZ6ZsBxYwMoGJGQCFjfSMTkDoLJDEzfXN0ZUA1UNNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID#" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"ID Count" = 
        Table.AddColumn(
            #"Changed Type",
            "ID Count",
            each
                let
                    varCurrentID = [#"ID#"],
                    varCurrentDate = [Date]
                in
            Table.RowCount(
                Table.SelectRows(#"Changed Type", each [#"ID#"] = varCurrentID and [Date] <= varCurrentDate)
            ),
            Int64.Type
        )
in
    #"ID Count"

 How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[amitchandak]

@cmaloyb , See if this can help

https://www.youtube.com/watch?v=7CqXdSEN2k4

In DAX refer subcategory Rank
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[Greg_Deckler]

@ImkeF , @edhans