How to get Sum

[Ann_Fajardo]

Hi, Everyone

Newbie here using power query seeking for your help how can I get total number of Partner Bank in the below screen shot per vendor
What I get is the total number of Vendor instead 😞

Thank you in advance

Ann

[mussaenda]

Hi @Ann_Fajardo ,

you are getting the count because the data type is text and when you grouped them, you didnt change the Count.

First change the data type of Rank type to Number.

Then group by vendor then select sum instead of count.

[Ann_Fajardo]

Hi Jasper

Thanks for the reply I have tried the sum as well but I got an Error

Ann

[JasperDJ]

Hi @Ann_Fajardo ,

The column you are applying a sum to has text in it aswell; like the "0001" value in your screenshot. If this is the only text value in this column, you could opt to change the type to Whole number before applying the Group By. If there are other text values that cannot be converted to a number you'll get some more errors 😛

--edit

forgot to mention I made a copy of the Per bank type column! Example:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVKK1cHGMAIyDHGyjIEsAwNcbBjPBEkGwjZCYhsrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Vendor = _t, #"Part bank type" = _t]),
#"Duplicated Column" = Table.DuplicateColumn(Source, "Part bank type", "Part bank type - Copy"),
#"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"Part bank type - Copy", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Vendor", "Part bank type"}, {{"Sum", each List.Sum([#"Part bank type - Copy"]), type nullable number}})
in
#"Grouped Rows"

[Ann_Fajardo]

Hi, Jasper

Is that the only thing that's why I have encounter that error if so what else can I do if I want to count also those not in whole number in column "Partner Bank"

Ann

[Anonymous · Accepted solution]

Hi @Ann_Fajardo 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdE7DsQgDATQu1CnsPEnmbNEKbJ7/zts2CbCg0TDE2Zs+TwbuiAh3rY2zrUNAqDaK0loJZ/JNA5MX1lPhU1lT17uXstMnqvOZEw708EEIlemMaCIFPWlRu3XkwRVopMYyTIvKC9l9S5phned92Kfn8VCv3+7fg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Vendor = _t, #"Part bank type" = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor", Int64.Type}, {"Part bank type", type text}, {"Count", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Vendor", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Count"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if [Part bank type]<>"" then Table.RowCount(Table.SelectRows(#"Removed Columns",(x)=> x[Vendor]=[Vendor] and x[Part bank type] <>"")) else null),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Vendor"}, {{"Count_vendor", each List.Max([Custom]), type nullable number}})
in
    #"Grouped Rows"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JasperDJ]

Hi Ann,

Ifyou use a Group By you can select a SUM instead of a COUNT and select a target column to summerize. Is this what you meant?