Forum Discussion

lavanyak's avatar
lavanyak
Advocate I
8 years ago

table.group

Hello,

I am facing issues with Power query Table.Group.

I am trying to get the sum of sales for each sector.In total I have 1700 sectors (OnD)), but I am getting results only for 358 sectors.I am not understanding where im going wrong.

 

Below is the query I used.

= Table.Group(tb_SalesData,"OnD",{{"TotalGMV", each List.Sum([TotalGMV])},{"TotalDisocunt", each List.Sum([TotalDiscount])}})

 

Below is teh sample output. 

OnDTotalGMV
ABT-CAI1766
ABT-CGK4798
ABT-COK2297
ABT-DAC3639
ABT-DMM10753.05
ABT-ISB1450
ABT-IST3029.999
ABT-JED9860.9

 

any help is much appreciated.Thanks in advence

Regards

Lavanya

Need Help

4 Replies

  • Gazzer's avatar
    Gazzer
    Resolver II
    8 years ago

    Can you clarify - do you have 1,700 unique sectors? Or does your table have 1,700 rows of data, with 358 unique values?

     

    Sorry if this seems like a patronising question, I just can't think of another way to explain it, based on such a small sample of data.

    • lavanyak's avatar
      lavanyak
      Advocate I
      8 years ago

      My data is at booking date level and has in total 26000 records, in which I have 1700 unique sectors.

      I am trying to get the sum of sales for each of these 1700 sectors by grouping at sector level. Ideally in the result I should get 1700 rows but I am getting the results only for 358 sectors.

       

      The data I gave is the sample of the results.

       

      Hope it is clear now.

      • Gazzer's avatar
        Gazzer
        Resolver II
        8 years ago

        Hi lavanyak

         

        Unfortunately, without seeing the actual data it is not possible to say why you are getting fewer unique values than you expect. All I can really suggest right now is to double-check that you are grouping by the right columns. Perhaps take the data into Excel and do some counts in there?

         

        Sorry - I realize this is not particularly helpful!