Forum Discussion

ousslaraichi's avatar
ousslaraichi
Frequent Visitor
6 years ago
Solved

Add a conditional count column

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.

 

IdAccount IdCreateDateNewColumn
1A20191
2A20182
3A20182
4B20191
5B20181

 

Thanks a lot!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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"

     

4 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Jimmy801
      Community Champion
      6 years ago

      Hey

       

      Great solution Anonymous 

       

      Jimmy

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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