Add a conditional count column

[ousslaraichi]

Hi, I am trying to add a new column to my query that will give me for each row, the count of transactions that occurred for an account for a specific year. In my example account A had two transactions in 2018, so for each Account A row created in 2018 I expect to see 2, all the rest is a one.

Id	Account Id	CreateDate	NewColumn
1	A	2019	1
2	A	2018	2
3	A	2018	2
4	B	2019	1
5	B	2018	1

Thanks a lot!

[Jimmy801]

Hello @ousslaraichi 

If these answers to your requested helped or solved your problem, please mark them as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Anonymous · Accepted solution]

This should be a fast solution for large tables.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYiMDQ0ulWJ1oJSOEgAVYwBhdwATIcULWYooQAKqIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, #"Account Id" = _t, CreateDate = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Account Id", "CreateDate"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Merged Queries" = Table.NestedJoin(Source, {"Account Id", "CreateDate"}, #"Grouped Rows", {"Account Id", "CreateDate"}, "Grouped Rows", JoinKind.Inner),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"NewColumn"})
in
    #"Expanded Grouped Rows"

[Jimmy801]

Hey

Great solution @Anonymous 

Jimmy

[Jimmy801]

Hello

see this code example. Used RowCount and SelectRows to make the calculation

let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYiMDQ0ulWJ1oJSOEgAVYwBhdwATIcULWYooQAKqIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, #"Account Id" = _t, CreateDate = _t]),
    AddColumn = Table.AddColumn
        (
            Quelle,
            "Count", 
            (add)=> Table.RowCount
                (
                    Table.SelectRows
                        (Quelle,
                        each [CreateDate]= add[CreateDate] and [Account Id]= add[Account Id]
                    )
                )
        )
in
    AddColumn

have fun

Jimmy