Excel - Power query: Show everything all customers, who bought X service, bought.

[Doge Robert]

Hello all.

I'm terribly sorry, if this question has been asked before, but I couldn't figure out the proper search keywords to get a result.

I have a dataset with close to a million rows by now... And growing.
In there, I have a column with customer ID. In another, I have a list of services bought.

To explain, what I'm trying to do, here is how I would do it with formulas:

Column 1 (Controlcount): If(@[Service]="X";1;0)
Column 2 (Check all): Sumifs([Controlcount];[CustomerID];@[CustomerID])

So basically, the first column types a 1, every time X service is listed in the Service-column
The second column totals the number of ones for each customer.

Then I can simply make a pivot and filter out every customer with a count less than 1.

But this isn't practically possible with a dataset as large as the one, I'm working with, so I'm looking for a way to get Power Query to do it for me, before the dataset is loaded.
(I can always change the service-filter inside Power Query, when I'm looking for a different service.)

One added criteria: I cannot do anything in Power Query, which will re-group filters in any way. I can only add new columns, not alter the existing ones or how they're outputted.

Is there any way to do this?


Regards

Rob

 

[sandy666]

use XL2BB to post representative example of source data and expected result

 

[Doge Robert]

use XL2BB to post representative example of source data and expected result

I'll write up a mock sample, as soon as I'm on my own machine and can download the add-in..

Thank you.

 

[sandy666]

sure,
also you can post a link to the shared excel file with representative source data and expected result via onedrive, googledrive , dropbox or any similar service