Right now there are basically two ways to pull data into a target workbook from a source workbook:
- Query it as a data connection (in which case you have to close the source workbook every time you want to refresh the data)
- Use an external link in a cell formula (in which case the source workbook has to be open if you want to get up to date data)

This is harder than it needs to be. Regardless of the method of pulling in external data you should be able to refresh it regardless of the status of the source workbook. Google Sheets =importrange() is a good example of this. I can pull in data from any source workbook and that data is live linked regardless of whether the source book is open or closed.

Either make it so that queries and external links both work regardless of the status of the source workbook, OR consolidate the method for pulling data in from an external workbook down to one approach and make that one independent of the status of the workbook. (As dynamic arrays roll out it will be more natural to pull a full array in from an external link, so IMHO the better option is to nuke OLE connections and just use external links that work irrespective of the status of the source workbook).

Note also that external references should always work via the URL of the target sheet in SharePoint / OneDrive (e.g. http:// ...). This will allow the link to remain valid even when changes occur to the location of the source file.

I don't want to be too critical, but to be honest sometimes it feels like MS is oblivious to the fact that gSuite is completely crushing MS on collaboration and cross-linked file features and support. MSO has some serious advantages of it's own, but in those areas you're trailing pretty far behind and are going to get left behind as more organizations transition to cloud-based working models.

It took us three months for Microsoft support to understand this simple request and conclude it is a "functional limitation" with current Excel and the linking of data only works on local c-drive, not over a shared onedrive folder between colleagues - sigh.

Here is the summary of steps:

1. Open an Excel file.
2. Click on Data.
3. Click on get Data.
4. Select from File.
5. Select From Workbook.
6. Imports an Excel workbook which has table inside it and load it to this file.
7. Once done, upload it to OneDrive from where this files gets to be shared.
8. Go to Shared tab for OneDrive Online and then select Shared with me and then open a folder inside which the same file is saved using Excel application.
9. Select sync option to sync that to his pc and then opens the same file from his pc
10. Once file is open by another user, right click on the table from the excel sheet
11. Click on refresh from the menu and this is the point where you encounter the error.

Предлагаю добавить в настройку определения числовых данных пункт "Кроме значений, обрамленных ограничителем строк".

В этом случае можно будет, выгружая данные из учетной системы в файл CSV, текстовые данные заключать в кавычки и они не будут искажаться при импорте в excel, даже если тип столбца установлен "general" (общий).

Делать это по умолчанию, без выноса в настройку импорта чисел, не следует.

У других пользователей может встретиться исторически сложившийся вариант, где в импортируемых CSV все данные, включая числа, заключаются в кавычки.

Также возможны CSV файлы, где разделитель полей и разделитель целой и дробной части чисел одинаковы.

В этом случае у пользователей тоже поломается импорт и числа станут текстом.

Поэтому только настройка, по умолчанию выключенная (настройку по умолчанию этой опции можно вынести в глобальные настройки excel)

Это позволит не просматривать столбцы разбираясь, в каких идут числовые данные, а в каких строковые идентификаторы, лишь похожие на числа.

hi
I would love to use Excel for day to day scraping but currently it's way too complex.
Both VBA and Power BI are not user friendly and you have to be advanced in Excel to use it.
It's frustrating and it forces you to use google search for solving problems.
I would like Excel to open browser window and allow me to select which exact part of the site I want to scrape (EVEN IF IT'S NOT A TABLE) then menu should open to select corresponding cell and frequency at which I would like to do it. Then format, set automated calculation etc.

Let's call it "Grab and Edit" method. Currently even with Power BI it's more like "Edit and Maybe Grab".

Please keep in mind that excel should be ideal software when it comes to scraping and somehow this topic has been neglected over the years.

Scraping is so common nowadays!

I'm importing data in 3 different tables and using that table data, I've created Pivot Tables. When I click on "Refresh All" its refresh pivot tables first and import fresh data later. This result in to Pivot table showing same data instead of updated table data as they are refreshing before query import data from external sources. Because of this sequence of refresh I've to click again on "Refresh All" button to update Pivot data. If user can manage the sequence of refreshing Tables & data (First let query import all data & later Pivot Table refresh data) so that user don't have to Click on "Refresh All" multiple times to get fresh data in all Tables/Pivot.

Having used the newfangled Queries and Connections pane for some time now I find it is time to add some suggestions

My issues:

1. The pane does not remember its size
It opens really narrow on my system, displaying only a (very) small part of the connection and query names so my first action every friggin time is to drag it wider.

2. No multi select
in the old connections window you can control or shift-click multiple connections to delete them. For maintenance this is a very useful feature I miss regularly.

3. It isn't application-centric
If I open a new workbook with connections, the taskpane disappears

4. No (or bad) short-cut key support
There seems to be no way to get the focus on the taskpane using the keyboard. F6 supposedly switches between the worksheet, ribbon, task pane, and Zoom controls but there is no way to tell if the taskpane has the focus as far as I could figure out.

5. No "All" category
I'd like an "All" category to pick from rather than either connections, OR Queries (and make that choice stick please)

6. Refresh button unavailable
If the taskpane is too narrow (or phrased differently: if it isn't rediculously wide) the small refresh button is unavailable.

7. Refresh is not on the fly-out
Why isn't there a refresh button on the fly-out pane when you hover over a connection/query?

8. No Fly-out for connections
Pity Old-school connections don't have the fly-out. No doubt something useful could be done with that.

1. Guys, why you call every previous step for every next step in Power Query, while refreshing this query preview? I.e. if I am at Get&Transform UI and click "Refresh Preview" on a Query where I have 3 steps, PQ calls step one 3 times, and step two 2 times. But if you call step one, you can use the results of the call for the next step AND for showing its result in the UI as well. Why you call previous steps so much??

2. Moreover, you inevitably call all the Queries, used in the refreshing query from p.1. So in the case above, if refreshable query calls other query (let's call it PrevQuery), you call PrevQuery 3 times! And this is for every Query in a chain... It just throwing a lot of time of your users who do not want to lose the benefits of working with your really cool UI.

It is not possible to prevent querying the whole query chain using Table.Buffer. But it would be great if you would use the result of call of previous queries in a chain while working in PQ UI, i.e. in preview mode.