Getting all files as separated quieries

[Islam]

Hello everyone

ok experts let me clear out what i need exactly 

let's assume this example

if i have a folder containing over 100 different type files

what i wanna do using power query is :-

1- get the folder path automtically no matter where folder is or to make it simple i wanna make it as a parameter and i\ll just change it on changing folder location

2- i wanna filter folder files to show only excel spreadsheets

3- finally i wanna add each table in each of excel files in step 2 to data model as a separated query table\

i can make the first 2 steps but for the third and final step i have no idea

[PhilipTreacy]

Hi @Islam 

The general idea with Power Query is to combine data into a single table.  What you are trying to do is combine 100 files into a single query (table) but then split them again into 100 tables in your Data Model.  Do you really need each file as a separate table in the model?  It doesn't sound like thr gith approach if the data is related as I'd expect it to be.

You could create a query that has as it's final result a column that consists of 100 rows with each row being a table.  But you would then have to create 100 queries to access the table in each row.

Regards

Phil

[Islam]

that's the point so i can't make a separated queries from single query or step

[PhilipTreacy]

Hi @Islam 

You'll have to put all the files in the same folder and then use the Get from Folder connector to load all files in the same query.  

If the files are spread around in different folders, they will load as different queries, 1 query per file.  Unless you write code that specifically reads a list of the paths and loads one by one.  This might be more work than is necessary when compared to moving all files into the same folder?

regards

Phil

[Islam]

ok sir let me clear out what i need exactly 

let's assume this example

if i have a folder containing over 100 different type files

what i wanna do using power query is :-

1- get the folder path automtically no matter where folder is or to make it simple i wanna make it as a parameter and i\ll just change it on changing folder location

2- i wanna filter folder files to show only excel spreadsheets

3- finally i wanna add each table in each of excel files in step 2 to data model as a separated query table\

i can make the first 2 steps but for the third and final step i have no idea

[Islam]

you mean that i create subfolder for each excel file as to be the only way to get each spreadsheet as a separated query ?