After a lot of trial & error and a lot of Grace I worked out following workaround that really works regardless of OneDrive synchronization status.

Explainer of the situation: When OneDrive for Desktop is synchronizing an Excel workbook (let's call it Data Workbook), and that workbook is trying to be accessed from another workbook (let's call it Analysis Workbook) via PowerQuery or even directly by regular pivot table (not even PowerPivot) - Excel will regularly throw out error The process cannot access the file because it is being used by another process. That happens because the import/reading mechanism wants exclusive access to the Data Workbook (even though it is reading it only). You can't do a thing about it.

Workaround: You have to "fool" the import mechanism into thinking it is not accessing another workbook. There is a good enough solution for this which can be applied and automated in most situations:

set up a simple single formula in Analysis Workbook that pulls all relevant data from Data Workbook, and then set up import mechanism to use that as a source. This successfully circumvents any and all issues with OneDrive. Details on the example of using regular pivot table as the data consumer in Analysis workbook:

Let's say your data workbook is My Data.xlsx, and an Excel table in that data workbook is the source of your data, and that table is named source_data. For the sake of simplicity, I'm presuming all workbooks are in the same folder. in Analysis workbook, add a worksheet "ds_trick", and in A1 cell place formula ='My Data.xlsx'!source_data[#All] (no matter how large the source data table is, it is going to fit into this worksheet because the formula is in A1 and there cannot be more rows in Data workbook's worksheet then in the Analysis workbook worksheet so you are safe on that side) Let's say that this data table fills columns from A to N. Change the pivot table data source to 'ds_trick'!$A:$N. Notice that you are not specifying the rows in this reference - you are referencing whole columns! make sure that whatever automation mechanism you are creating is opening the Data workbook before you are ordering pivot cache refreshing. Notes:

Pivot tables do not support named ranges as source so you cannot directly reference the data workbook's Excel table in it. It also won't work if you try to create a named range in analysis workbook that references table in data workbook. You have to pull the data via formula in Analysis workbook. Without named range as data source for pivot table you are left with "fixed" number of rows to define which is not good, so the trick for that is to reference whole columns - luckly pivot tables are good with trimming empty rows so this actually is done without any latency in processing input data. This way you are successfully faking dynamic named range as source for pivot table (in terms of rows)