assistance stuck index and match in power query file

[roger99]

Hello Everyone

I used sumproduct with index and match for summarising data on a report sheet based on breakdown sheet of same workbook

The columns involved are column C, D, E , F and H .

I am newbie to power query but trying to replicate the same breakdown formula file in power query is partly successful for the breakdown sheet .

I am stuck in power query how to index the itemised column in report sheet into breakdown sheet .

Can anyone one assist how to allocate in power query the itemised columns of report sheet to breakdown sheet .

the criteria are column A , B,C of report sheet to breakdown sheet column C , D ,E.:lever:.

Attached formula method used and power query file I used where I am having issues.



Thanks

 

[AliGW]

I'm not sure what you are expecting to be able to do in PQ, but the query is not going to create a column for you in the breakdown sheet. It seems to me that you still need to do this with a formula.

 

[roger99]

ok thanks for info , i thought it can be done in the M lanaguage, as i find it very powerful as i deal with lot of big and tedious queries

 

[AliGW]

It probably could be done, but not in the way you are expecting, I believe.

The preoblem is that you are creating a results table from a source table, and then expecting to be able to somehow bring something back into the source table. Even if it were theoretically possible (I’m not saying it isn’t), there would be the danger of PQ’s equivalent of circular references.

It would make more sense to have two output tables from two queries, possibly making one of the queries connection only. Much simpler, though, to create that final column manually, I think.