Forum Discussion
How to get Sum
Hi Anonymous
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.
Hi Jasper
Thanks for the reply I have tried the sum as well but I got an Error
Ann
Hi Anonymous ,
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"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
Hi Anonymous
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.
