Forum Discussion
Power query: Custom Columns
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.
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.
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.
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.
