Filtering between 2 numbers then getting the sum of another column

[student5000]

Hello,

I have a simple table here. 

 

What I am trying to do is create a new table that sums the amount when the account number is between 2 numbers. So for example when account is between 4010 and 4200 then sum Amount. The reason that I am looking to do this in query editor rather than on visualization side is because I will need to create many new tables using this same filtering method because I would like to display many of the different summed amounts all at once.

Any and all tips are appreciated.

[v-jingzhang · Accepted solution]

Hi @student5000 

For your current requirement, you can create a duplicate query of the original table in Power Query Editor and execute the following steps:

1. Filter rows by Account column with Number Filters > Between. 

2. After filtering, select Amount column. Under Transform tab, select Statistics > Sum to calculate the sum of Amount column.

3. In this way, you get a single sum value. You can convert this value to a table.

Attach all codes below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMjIAUrE60SAGmGsI4RpDuUYQLkSxMapiYwMTCNcEzDUBy8YCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Amount = _t, Account = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Amount", Int64.Type}, {"Account", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Account] >= 2000 and [Account] <= 3000),
    #"Calculated Sum" = List.Sum(#"Filtered Rows"[Amount]),
    #"Converted to Table" = #table(1, {{#"Calculated Sum"}})
in
    #"Converted to Table"

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[AlexisOlson]

I'd strongly recommend against trying to "create many new tables using this same filtering method" in general. Can you show us what your final intended display is?