Forum Discussion
How to get Sum
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 😞...
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
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.