Hi all!
I'm having an issue with a file I created. I was asked to create a dashboard to show some metrics across multiple tables (on multiple sheets), and the most efficient way that I found was using Power Query. I set up 2 queries to pull data from 2 different sets of tables. The method I used for creating these queries is from this site, because I'm not super familiar with Power Query. This includes using the work-around to prevent recursion and your data doubling every time you refresh it.
On the desktop version of Excel, it works great. Everything as intended. Refreshing does nothing. However, the person whom I made the file for uploads it to a shared drive where it is edited by multiple employees, so it ends up being hosted on the browser version of Excel. I believe that is where the issue lies. When the query is refreshed, usually not the first time but maybe the second or third, it adds columns. This then causes the formulas to all pull from the wrong column.
This is the queries sheet on desktop Excel, where it all shows correctly:
And here it is on the browser version, after being refreshed a few times:
So if I have a formula referencing the Customer column, after it refreshes and messes up the columns, it'll instead reference the S/O # column. I did check the table design settings on desktop Excel and "Preserve column sort/filter/layout" is checked, but on browser there are no settings for this. Is there a way to stop this from happening? Unfortunately my solution of "just re-paste the correct formula when refreshing messes it up" didn't seem to be popular when I suggested it.
I'm having an issue with a file I created. I was asked to create a dashboard to show some metrics across multiple tables (on multiple sheets), and the most efficient way that I found was using Power Query. I set up 2 queries to pull data from 2 different sets of tables. The method I used for creating these queries is from this site, because I'm not super familiar with Power Query. This includes using the work-around to prevent recursion and your data doubling every time you refresh it.
On the desktop version of Excel, it works great. Everything as intended. Refreshing does nothing. However, the person whom I made the file for uploads it to a shared drive where it is edited by multiple employees, so it ends up being hosted on the browser version of Excel. I believe that is where the issue lies. When the query is refreshed, usually not the first time but maybe the second or third, it adds columns. This then causes the formulas to all pull from the wrong column.
This is the queries sheet on desktop Excel, where it all shows correctly:
And here it is on the browser version, after being refreshed a few times:
So if I have a formula referencing the Customer column, after it refreshes and messes up the columns, it'll instead reference the S/O # column. I did check the table design settings on desktop Excel and "Preserve column sort/filter/layout" is checked, but on browser there are no settings for this. Is there a way to stop this from happening? Unfortunately my solution of "just re-paste the correct formula when refreshing messes it up" didn't seem to be popular when I suggested it.
