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 transaction...
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"

     

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