Array formula to PQ

[RaudMees]

Hello

I made a formula in Excel sheet, but now I need to convert it to PQ, but.. it will give several errors (PQ don't like @ ; and $ symbols when I paste it to PQ). Is there any option to translate it to PQ-friendly? When it's in sheet, then my table is running very slow (data is coming from CAD-software and calculating all the time makes it slow).
{=IF([@Kogus]<>"";IFERROR(INDEX($J$1:$J7;MATCH([@Alamkoost];TRIM($E$1:$E7);0))*[@Kogus];[@Kogus]);"")}

WBR
RM

 

[RaudMees]

Yes, but when I have empty rows, then 1 error is for each row. This is template file and first table will have 4000 rows.

 

[sandy666]

if you've empty rows (whole row is empty) so in the first step filter it by null/blank then the rest should work

see again my example (updated) from post#18

 

[RaudMees]

Yes, now no errors at all Looks almost done. I made some sheets more and now I am in trouble to merge those sheets. They have to be side by side, like on picture. This sample is made manually by producing company. Do I need unique column to merge them?

 

[sandy666]

it depends what you want to achieve, there are the same headers so if you use Merge (with additional column: Index) you will get a lot of columns, so I think Append is a better choice (start with the table with the most columns)

 

[RaudMees]

I tried with Append function, but it put data from top to bottom, not from left to right.

 

[sandy666]

as I said: it depends what you want to achieve
if you don't like Append result add to each table Index then Merge by Index
you didn't even show the expected result so how do I know what to do

 

[sandy666]

maybe add new column with the name of each table, 1st table - column Name filled by Karbiku kaas, 2nd table - column Name filled by Karbiku kips, etc, then Append and create Pivot Table
Big horizontal table doesn't make sense to me
[edited]

 

[RaudMees]

Ok, will try. That picture what I sent, this is the needed result.

 

[sandy666]

post#27 was edited
btw. you cannot use merged cells in Tables
and I am still talking about Power Query not about any formula(s)

 

[RaudMees]

Finally I just copied those tables side by side on Summary sheet and they works. I made video too of this: http://tehnik.ee/PQ.mp4
Is there any better option to refresh all tables at once than VBA code?