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.

​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.   Read More

​