Hi all,

I’m using Power Query to pull data from other Excel files. As the name of these files change every day, I’ve worked around this by using using a cell (named range) and pulling it into Power Query, and using the variable defined there to define the path and file name to get.

Now, one of the problems happen:
1. If I leave the Named Range (Date) with the same name as the Power Query (Date), every time I use the Named Range in a function, Excel automatically changes it to be “_xlpq.Date” resulting in a #CALC! ERROR.

To circumvent this, I changed the names of the variables in Power Query so they would differ from the named ranges.

Great. Now I can use the named ranges without issues.

2. But then I noticed that when I updated the workbook with Refresh All, the Power Query variables linked to the named ranges were /not/ updating. To update them, I had to go to the Power Query editor and refresh the preview one by one.

To be clear, upon changing the PQ variables’ names to match the named ranges, they were updating with Refresh All.

Any help to bypass one issue or the other is welcome. It seems the way forward would be to force Excel to stop updating the formulas with the “_xlpq.” prefix on the shared names, but I don’t see how to do this.

If you think this is an oversight in Excel’s functionalities, how can I share feedback?

Thanks,

J Ruivo

​Hi all, I’m using Power Query to pull data from other Excel files. As the name of these files change every day, I’ve worked around this by using using a cell (named range) and pulling it into Power Query, and using the variable defined there to define the path and file name to get. Now, one of the problems happen:1. If I leave the Named Range (Date) with the same name as the Power Query (Date), every time I use the Named Range in a function, Excel automatically changes it to be “_xlpq.Date” resulting in a #CALC! ERROR. To circumvent this, I changed the names of the variables in Power Query so they would differ from the named ranges. Great. Now I can use the named ranges without issues. 2. But then I noticed that when I updated the workbook with Refresh All, the Power Query variables linked to the named ranges were /not/ updating. To update them, I had to go to the Power Query editor and refresh the preview one by one. To be clear, upon changing the PQ variables’ names to match the named ranges, they were updating with Refresh All. Any help to bypass one issue or the other is welcome. It seems the way forward would be to force Excel to stop updating the formulas with the “_xlpq.” prefix on the shared names, but I don’t see how to do this. If you think this is an oversight in Excel’s functionalities, how can I share feedback? Thanks,J Ruivo  Read More

​