Excel 365 - dynamically changed path for "Master File" - Power Query

0
votes

I have got 2 files saved on my Desktop, let's call them:

  • Master File;
  • Slave File x*;

x - I want to be able to create x copies of this file.

For now by using formula Index, Match and I'm pulling data from tables in Slave File which are connected to the Master File by Power Query.

Master File got data -> Slave File (Power Query) transfers it into tables -> pull content using Index, Match.

Problem:

I want to share this file to my team but when I copy folder which includes both files, to the shared drive it says that it is looking for my Master File saved on my desktop. In perfect world I want to all my teammates got both files (or x copies of Slave File) wherever they want to have them.

Example:

  • Teammate 1:

Saved Master File in Folder A;

Saved 10 copies of Slave File in Folder A;

  • Teammate 2:

Saved Master File in Folder B;

Saved 5 copies of Slave File in Folder B.

Teammate 1 and 2 both will update Master File but with different data, it depends on what they need and want to copy in it.

Solution:

I want them to be able to update their copy of Master File and then their copy of Slave File to be able to pull data from their Master File no matter where it is saved.

What I tried:

I googled that I can change parameters in Power Query but I definitely did it wrong since nothing changed. I also found INDIRECT formula but after 34 attempts to implement it to my formula, I gave up.

=(INDEX(Tab_Name1[[Table1]:[Table1]],MATCH(1,($B$2=Tab_Name1[[Table2]:[Table2]])(C$4=Tab_Name1[[Table3]:[Table3]])($A5=Tab_Name1[[Table4]:[Table4]]),0))

Extra Note:

I'm trying to avoid using VBA solutions as far as it is possible.

1
excelexcel-formulapowerquery
Referencing other files with INDIRECT-formula is often a pretty good way to go about things - just keep in mind that you need to have the workbooks that you are referring to open when you refresh the data. This is true for any direct references to external notebooks in excel. Have you tried keeping the "Slave File" and "Master File" open at the same time when implementing the INDIRECT -formula? – Prebsus
Hello Sir! Yes, I did it but the problem is that if I'm going to use INDIRECT formula, it means that I cannot use Power Query for connection, am I right? INDIRECT foreces me to get data directly from other file, not from Power Query created table. – Vulgaris Magistralis
If you're looking to use the INDIRECT function on a resultant table from a Power Query connection in one of your workbooks (Slave or Master), you can certainly do that. May I as, what is going on in your MATCH-formula above, particularly the use of = inside the match function? What are you trying to accomplish with this? – Prebsus
B2 = date cell, C2 = Product Name cell, A5 = Store ID cell. I have data for multiple Stores, multiple Products and dates. In my Slave file I want to pull data for the date I want (eg. 1/4/2020), for Store I want (eg. ID 123) and Product I want (eg. ID ZXC). I'm using it instead of VLOOKUP because it allows me to use multiply criteria (3 in my case). – Vulgaris Magistralis
I fully support the use of INDEX/MATCH over VLOOKUP, and I see what you mean. I'm not fully able to reproduce your setup yet, but might be able to later. Hopefully someone else can hop in on this question as well though. In the meantime, good luck! – Prebsus

1 Answers

0
votes

For now I figured out two solutions how to get over my problem, maybe someone will find a good use of it.

Solution 1:

Simply as hell. Once moving both files, open Slave File, open Power Query Editor and in Source Type press edit - there you can choose new path of Master File and viola.

This works okay but it is still manual labor and it is only 50% what I wanted to achieve.

Solution 2:

Use INDIRECT function is good idea but a bit more complex. In Power Query editor you can edit formulas which connects one file to another but adding INDIRECT to the localization path formula. Unfortunately you have to do it as many time as many connections you have (in my case it was 6).

Summary:

For now I don't have perfect solution but in my opinion the Solution 1 is the best so far because it requires minimum work and knowledge for other users.