table.group

[lavanyak]

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. 

OnD	TotalGMV
ABT-CAI	1766
ABT-CGK	4798
ABT-COK	2297
ABT-DAC	3639
ABT-DMM	10753.05
ABT-ISB	1450
ABT-IST	3029.999
ABT-JED	9860.9

any help is much appreciated.Thanks in advence

Regards

Lavanya

[Gazzer]

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]

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]

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!

[lavanyak]

Thanks for your response.

I crossed checked the data, using the same table if I group sales by sector in Power bi desktop visualization, I am getting total sales for all 1700 sectors.

I dont understand why I am not getting it in power query.