Forum Discussion
Solved
power query with relative data source not refreshing accurate data
I have a query that is referencing a relative data source that works fine when that source is a specific file. However when I try to direct it to a specific table within that file it isn't reading t...
Hi Bridgett_B
I think you could continue using your CELL formulas, it's a clever idea.
To avoid the issue you describe when opening another workbook: Just add a cell reference to the formulas:
This way, it will always refer to that cell in this workbook, and not to another workbook.
Yes and it will update correctly if I save the workbook after making changes to the table data. If I don't save the workbook, it won't refresh with accurate data.
I did finally figured out what it's doing. For examples sack lets say the workbook I have my query in is workbook A. My relative data sources in workbook A are setup on a designated "settings" tab and are various (CELL,"filename") formulas that are giving the file path and file name of the current workbook. When I open another Excel workbook, let's say workbook B, those formulas in the workbook A are updating to reflect the file name and directory path of workbook B. I think I'm going to have to either hard code that file name in, which could be problematic, or make it a parameter that the user can change/update on their own.
I did finally figured out what it's doing. For examples sack lets say the workbook I have my query in is workbook A. My relative data sources in workbook A are setup on a designated "settings" tab and are various (CELL,"filename") formulas that are giving the file path and file name of the current workbook. When I open another Excel workbook, let's say workbook B, those formulas in the workbook A are updating to reflect the file name and directory path of workbook B. I think I'm going to have to either hard code that file name in, which could be problematic, or make it a parameter that the user can change/update on their own.
Hi Bridgett_B
I think you could continue using your CELL formulas, it's a clever idea.
To avoid the issue you describe when opening another workbook: Just add a cell reference to the formulas:
This way, it will always refer to that cell in this workbook, and not to another workbook.
- Great, thanks for your help!