SOLVED

Power query: Custom Columns

Copper Contributor

Hi, 

 

I am trying to use power query to create custom columns with formula.

 

The file I am using has six power queries. 

I have appended the six queries and removed columns and duplicates.

But I want to add custom columns with formula like below image.

Sunbed1060_0-1719209093247.png

 

There are two formulas for each column (item# and qty#):
item columns: 
=IF(IFERROR(SUMIF(INDIRECT("'" & J$2 & "'!J:J"), $E13, INDIRECT("'" & J$2 & "'!R:R")), 0) = 0,"",IFERROR(SUMIF(INDIRECT("'" & J$2 & "'!J:J"), E13, INDIRECT("'" & J$2 & "'!R:R")), "Not Found"))

 

qty coulmns: 
=IF(J13="","",IFERROR(SUMIF(INDIRECT("'" & J$2 & "'!J:J"), $E13, INDIRECT("'" & J$2 & "'!Q:Q")), "Not Found"))

 

The reason of these formula is that user can type sheet name.
Then it will show the corresponding code and supplier and show the item and qty based on the sheet name.

 

Is this doable in power query? or is it better to do script to accomplish this?

 

Thank you all in advance.

 

1 Reply
best response confirmed by Sunbed1060 (Copper Contributor)
Solution

@Sunbed1060 

If you started with Power Query it's logical to do everything with Power Query. The only each time user added new sheet name Refresh All shall be done.

Not clear do you query other sheets or not, what is E13, etc, are other sheets dynamic (i.e. you add / remove them from time to time) or static. It's better to have small sample file or at least more detailed screenshots to discuss. 

1 best response

Accepted Solutions
best response confirmed by Sunbed1060 (Copper Contributor)
Solution

@Sunbed1060 

If you started with Power Query it's logical to do everything with Power Query. The only each time user added new sheet name Refresh All shall be done.

Not clear do you query other sheets or not, what is E13, etc, are other sheets dynamic (i.e. you add / remove them from time to time) or static. It's better to have small sample file or at least more detailed screenshots to discuss. 

View solution in original post