I have customers with a few different accounts. The Main Account is Partner and the sub accounts are Customer. In Excel I can use the =TRANSPOSE(FILTER(U2:U816,T2:T816=A2,0)) and it will filter across
Partner Code Customer1 Customer2 Customer3
010022 0100420 0100422 3600700
010026 0110050
I am trying to set it up on Power Query so it will automatically update but I can't get the right function.
I've added the Excel formulas to your file so that I could compare the PQ outcome. The query is quite basic though requires some manipulation of the M-code beyond just clicking in the user interface.
See if you can follow the steps. If you run into problems, just come back here.
Hi Riny, thank you very much, this is Spot on!!
Hi Riny, just one other Query. Is there an easier way to now merge my Sales Data with the Transpose Filter Table, see attached. I can do it by xlookup all the way across, but it is very time consuming.
ross, but it is time consuming.
Could you upload the data table as you had it in your original post. And the sales table that 'belongs' that that data?
I.e. the one that leads to the first grouped) table in your new file. I believe you don't need the first step as it will only complicate the matter.
Thought very hard about it and couldn't figure out a manner to achieve what I believe you want in the way you described. But there may be a better way to match sales with Partner/Customer codes. To be honest, I find your desired end result difficult to interpret.
I can't dictate how you should analyse your data, but I think the attached file offers a neater solution. It merges the Sales with the Codes in PQ and outputs a pivot table. I've color-coded the results for "Baby Feed" in the pivot table to compare it to your own analysis.
I'll let you be the judge.
Thanks Riny for your time, this does work a lot better. Eileen
Great! After I posted my previous answer, I thought that you can actually achieve a very similar result without the need for PQ. If that's OK for you consider the example in the picture below. Add a column to the Price table that takes the Partner code for each Customer from the Code table. A simple XLOOKUP will do that. Filter out the ones that are not found ("??") and you are left with the same results as the pivot table from PQ.
Hi Riny, it is confusing but this works brilliantly. Some customers allow all sales through their main account, but some customers want a different account for different products! So now I have added an extra column to your solution and done a Groupby in PQ. Thank you so much!!
Eileen
