In the table below, I need to get a unique list of IDs with a count of how many were sold
In the example below, ID #1 was sold twice, #2 zero, #3 one, #4 zero, #5 one.
In order to do this, I have to group it by ID and aggregate with count
But doing so results in 2 for #1 , 1 for #2 (instead of zero), 1 for #3 etc.
In the final result, I do not need the "SoldTo" column, but I do need the Description and OnHand
<tbody>
</tbody>
How can I achieve this with power query
Thanks,
David
In the example below, ID #1 was sold twice, #2 zero, #3 one, #4 zero, #5 one.
In order to do this, I have to group it by ID and aggregate with count
But doing so results in 2 for #1 , 1 for #2 (instead of zero), 1 for #3 etc.
In the final result, I do not need the "SoldTo" column, but I do need the Description and OnHand
ID | Description | OnHand | SoldTo |
1 | ABC | 10 | John |
1 | ABC | 10 | Joe |
2 | DEF | 5 | |
3 | GHI | 3 | Fred |
4 | JKL | 1 | |
5 | MNO | 6 | Joe |
<tbody>
</tbody>
How can I achieve this with power query
Thanks,
David
Last edited: