Forum Discussion
GroupBy function to include zero values
Hi,
I am looking to group my data of InvoicedQty by Yearmonth. I need the grouped sum data to include zero values.
I have data in the following format:
| YearMonth | InvoicedQty |
| 2019/01 | 100 |
| 2019/02 | 25 |
| 2019/02 | 35 |
| 2019/02 | 32 |
| 2019/02 | 14 |
| 2019/05 | 52 |
| 2019/05 | 13 |
| 2019/06 | 52 |
I would need the outcome to look like this:
| YearMonth | InvoicedQty |
| 2019/01 | 100 |
| 2019/02 | 106 |
| 2019/03 | 0 |
2019/04 | 0 |
2019/05 | 65 |
2019/06 | 52 |
I have succeeded in grouping the data but seem to not be able to make the YearMonths with zero invoicedQty to not dissappear. Any tips?
-Jesper
6 Replies
Hi Anonymous ,
Do you use a separate calendar table?
Thank you.
Hi Dinesh_Suranga ,
I have a separate table containing all possible YearMonth values but am having trouble connecting it to the group function. Did you have some idea in mind?
Thank you.Hi Anonymous ,
Do you want to create this in the power query side?
Thank you
Hi Dinesh_Suranga ,
This does work, it leaves the value blank. My apologies, my purpose did not become clear earlier. I need to then use the dataset to create a r visual. want to create a forecast. The dataset I need for the R visual needs to contain a 0 for the months with no InvoicedQty, not blank. To my knowledge this does not fix this issue.
Thank You.
