Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 😞

 

Thank you in advance

Ann




Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

6 Replies

  • JasperDJ's avatar
    JasperDJ
    Helper I
    2 years ago

    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?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Jasper


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

    Ann

    • JasperDJ's avatar
      JasperDJ
      Helper I
      2 years ago

      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"

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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

  • mussaenda's avatar
    mussaenda
    Community Champion
    2 years ago

    Hi Anonymous ,

     

    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.