Array formula to PQ

RaudMees

New Member
Joined
Dec 9, 2019
Messages
17
Office Version
2016
Platform
Windows
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,300
Worksheet formula has nothing to do with Power Query
you can try Merge option
 
Last edited:

RaudMees

New Member
Joined
Dec 9, 2019
Messages
17
Office Version
2016
Platform
Windows
Yes, that was my question, how to make it suitable for PQ. Merge.. I will add a picture, how the calculation have to work. Data is shifted and named in different rows. You don't understand that language, but follow colors :)
PQ2.png
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,300
PQ doesn't support colors ;)
Picture is not readable, post shared excel file with representative source data and expected result via OneDrive, GoogleDrive or any similar
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,300
I read comments from the picture but nothing clearer
Code:
Column A contains names to differentiate products. A module is a compiled product that consists of the details below the word Module. One project can have 1-200 modules with different transitions as well as similar ones.

A row of module details, the quantity of which must be multiplied by the number of modules.

The module is called W92 (product type) and its dimensions.

The number of details in a particular module that need to be multiplied by the number of modules in a row

Number of similar modules
 

RaudMees

New Member
Joined
Dec 9, 2019
Messages
17
Office Version
2016
Platform
Windows
Column A is "Moodul". Same row in column E is the name of that Moodul and quantity in column J. On the next rows where name is same in column C as in column E, quantity must be multiplied with number in column J from row Moodul. At the moment all parts to assembly Moodul is for one assembly, but I have to produce more parts to assembly more modules. If I have 6 modules, then all parts have to be multiply with 6.
 

RaudMees

New Member
Joined
Dec 9, 2019
Messages
17
Office Version
2016
Platform
Windows
All other things are working well. Problem is just with those Moodul parts. I changed numbers manually, how the result must be in column J.
1575980803151.png
 

Forum statistics

Threads
1,085,789
Messages
5,385,902
Members
401,975
Latest member
guy6

Some videos you may like

This Week's Hot Topics

Top