Power Query Change “shift cells down” to “insert row”

[andydtaylor]

Hi

When Power query tables return more (fewer) rows than previous it shifts cells down (up) below the table. I would instead like Excel to insert (remove) an entire row. This is because of the way the rest of my workbook is structured. Is there a way that I can select or effect this behavior? FYI Typically I am hitting the “refresh all” button to update.

Thanks

Andy

 

[Matt Allington]

In short, no. Power Query returns a table. A table is more like a database than a spreadsheet. Order is not defined and hence you can’t rely on the order, and hence you can’t relay on the cell reference location. Consider instead loading the results to the data model and accessing the results from there.

 

[andydtaylor]

In short, no. Power Query returns a table. A table is more like a database than a spreadsheet. Order is not defined and hence you can’t rely on the order, and hence you can’t relay on the cell reference location. Consider instead loading the results to the data model and accessing the results from there.

Hi,

I may have a workaround which I will share (if so). I am exporting summary tables to worksheet purely for audit trail purposes. Power query being moon science after all....

Thanks,

Andy