Forum Discussion

dmt10's avatar
dmt10
New Member
5 years ago

Keep power query columns in sync with excel columns

I have used powerquery to combine several tables, perform some basic calculations, and produce an output table which is then loaded into excel. 

 

There are some additional calculations which it is preferable for me to perform outside powerquery - instead just using normal formulas in the columns next to the powerquery output table. 

 

This seems to work, the formulas complete succesfully and produce the expected output. 

 

However, when I refresh the query, I am getting the error "Microsoft Excel can't insert new cells because it would push non-empty cells off the end of the worksheet". I have tried every fix I can find (including deleting 500k outer rows/columns), but no success. 

 

What is the fix for this? What is the recommended way to produce a powerquery output table, and then add additional columns to that output table using formulas entered in excel? 

Need Help

2 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    5 years ago

    You need to add the columns directly to the Power Query output table. Power Query will do its thing on the first X columns, then your 2-3 additional columns in the same table will do their thing. Power Query will automatically delete rows or add rows as necessary, and the Excel Table feature will automatically grow/shrink your forumula coluims accordingly.

    Do to it, just start keying your formula immediately to the right of the last column in the PQ table. Do not skip columns in Excel.