Power Query - Help!

[TG2812]

Hello,

I'm trying to use Power Query Editor. However, it's the first time I'm using it and despite following some online tutorials, I have not gotten anywhere close to what I want to do.

Here is the challenge I'm facing;

• I have different vendor each reporting their sales/business objectives in separate Excel spreadsheets, all located onto the same OneDrive folder.
• I want to consolidate all their data into one Master Workbook. Basically I'm trying to establish a connection between this Masterfile and each separate excel spreadsheets.
• I'm just trying to establish a connection and retrieve the given number. I do not want to have a table format as a result of the connection.

What am I trying to obtain?

• in cell T5 (from the attached picture - IMG 4400), I should retrieve the number 480
• in cell U5 (from the attached picture - IMG 4402), I should retrieve the number 342
• etc..

The worksheets are set up the same way than on both pictures.

Note: I'm using columns grouping function in the masterfile...in case this has an impact

Thank you in advance for your help and inputs (preferably in an Excel spreadsheet please)

 

[RoryA]

Power query is only going to return a table of results. It looks, from the little I can tell from those pictures, like your best bet will be to return the relevant total rows/columns from all the files as a table in the master workbook, then use formulas to lookup the relevant values.

 

[TG2812]

Thanks for your reply
What kind of formula would you advise in my case? Would that create a problem if the vendors’ templates are moved from a folder to another on that same SharePoint ? Or would that create broken links and subsequently troubles to remap everything ?

 

[RoryA]

They would be lookup formulas of some sort.

If you move all the files, you would need to update the source path in the query you use. I would generally make that a parameter obtained from a cell so that you can just edit the cell and then refresh the queries.