Query Spreadsheet Based on Column

[Anonymous]

Hi,

I've got a folder containing about 700 spreadsheets. These contain data specific to a worksite. I also have another spreadsheet which contains some summary data for each of the sites. The names of the 700 spreadsheets are unique identifiers which are in the summary data spreadsheet. I'm hoping to build a report which will allow the user to click on the name of a site and will be taken to a report based on the full data (from the relevant spreadsheet in the folder). All spreadsheets have the same format and will need the same calculations applied to them.

Is this something that's possible in PowerBI?

Thanks!

[Anonymous]

Yes. This is possible. Using  Power Query / M, you could process all the files in a folder and consolidate the data into a table inside the Power BI desktop. Subsequently, you could build reports that give the functionality that you have mentioned.

Start with this path.

[Anonymous]

Hi, 

I can import all of the tables using this functionality, however I'm unsure how to use the column value to query the values from the corresponding table. Any suggestions?

[Anonymous]

---

@Anonymous wrote:

Hi, 

I can import all of the tables using this functionality, however I'm unsure how to use the column value to query the values from the corresponding table. Any suggestions?

---

This has nothing to do with the fact has you have the data spread across many files. Once you bring all the data into a table, you could use DAX to get the correct results.  Please provide more details about your table structure (fields) and a sample of the output you need. Also, mention exactly where you are facing the problem of finding it difficult to write DAX. If possible, please post your DAX code. Someone will be able to help you, once you provide these details.