Forum Discussion
Merge columns of tables
If you are on Excel 2016 or later Power Query is built-in, it's called here Get & Transform under the Data tab.
Did you consider Power Query?
The problem I am having with powerquery is that if I move the file to another place, it will not find the query. In my case the tables I am using in the query are located inside the workbook file itself and if I move the file or share it, the query wont work anymore. Another problem is that I have to save the file and then refresh the query which is not a major problem but its annoying.
Is that for any file or for some specific one? All these looks strange. Perhaps you may submit small sample file with such queries to check the issue is in queries or in your environment. Or both.
The file is very large and full of data I cannot share, let me try to explain again.
I am adding queries based on tables in the same worksheet, there is no secondary worksheet, I am adding data in Powerquery by choosing excel worksheet then the same worksheet I am using.
Problem with that is, it wont find the data if I move the file from a place, since it does not recognize it not the same worksheet but a worksheet in that directory. Also i must save and update the file to refresh the query, which makes sense but its annoying.
If all is in the same file, while don't you use
Source = Excel.CurrentWorbook()
and select tables from the navigation returned. You don't need file name with this.