Power query to pull a specific cell on a specific sheet

[Reecenorman1996]

i have around 70 excel workbooks each employee has 1 for every month all identical and saved in there own folders under a master folder i need to pull the total average from every one , i can can them to show on PQ but its shows all the other values in that column which i don't need , any help would be much appreciated

 

[Matt Allington]

I assume you mean “keep the number 73.9” in this example. to do that, you need to find a way to filter just that row. It seems to me you could filter on “% towards“ and keep only rows that =1 to achieve this.

Personally, I would load all the data in case you need it later

 

[Reecenorman1996]

I assume you mean “keep the number 73.9” in this example. to do that, you need to find a way to filter just that row. It seems to me you could filter on “% towards“ and keep only rows that =1 to achieve this.

Personally, I would load all the data in case you need it later

thank you ill give this a try

 

[jdellasala]

When you pull data in from the folder, you'll first be presented with a table like this:

Note that you should filter this table to prevent unwanted files from being included in the list before expanding the Content such as converting the Extension column to lower case, filtering it for only the extension you want, and filtering the Name if the files you need have names that have a pattern.
Once you've made sure you have only the files you want, click the double down arrow in the top right corner of the Content header. Power Query will then generate a function and pass all the individual files through that function and combine (Append) them. You'll end up with new Queries, a Function, and a Sample File like this:

Do NOT edit the fx Transform File function! Editing the Transform Sample File query will modify the fx Transform File function and perform the changes you made to the Transform Sample File query to all the files selected in the folder. If the structure of all the files IS identical, you should be able to transform all of the tables into the cell you need, and the final query will have two columns - a Source.Name column with the name of the source file, and Column1 which will have the cell you extracted using the Transform Sample File query.

Once you do it, it won't seem so confusing.