Working with multiple excel files as data source

[rushekkgadde]

Hi, is there a way to merge all excel files as separate sheets into one file and make a single connect into power bi, currently my queries are connected to single excel file, one query one connection, i want to change it to one excel file multiple sheets and one connection

[v-yulgu-msft]

Hi @rushekkgadde,

Here is a blog shows that get data from a SharePoint folder which contains multiple Excel files.

Regards,

Yuliana Gu

[Stachu]

when you connect to an Excel file usually 3 steps are generated in PowerQuery:

Source

Navigation

Changed Type

if you go to Source you should see the list of all sheets & named ranges in the file. Navigation drills down to single sheet - instead of that you can filter the sheets you need and expand them as tables - assuming they have the same layout. If they don't then it can still merged them but the structure may be very chaotic

[p0nk]

You can set a file as a data source and then each file in it will be imported into Power BI. When I was using this method I found it didn't work with Excel files, but CSVs worked fine so you may have to do some minor conversion (re-saving as a CSV). As long as they have the same column headings, they should be merged into one large data source.

[andruf]

I think I've run into this same issue trying to use the network drive folder holding several excel files as a source but I don't know if it is possible. The solution I had to settle on (unless someone has a solution here) was to upload each excel and append them into an already existing excel data source within Power BI.

In my case it is a weekly dataset I have to pull from the servers then upload into Power BI so I do get data > select the new excel > upload > edit queries > append and then select the original file you're appending into and then it appends the new excel into the full appended excel using all the previous weeks. 

It works well enough and the process only takes a few minutes plus all my conditional columns, grouping and measures are still in place from the primary excel source that is being appended into.

Hopefully I am still working inefficiently and someone has a work around to automate this better for both of us in this situation.

[rushekkgadde]

I have designed a dashboard with multiple csv file as inputs to different queries, now I want to consolidate all excel files into different sheets in one file and make a single connect, is this possible, or how can this be done in power bi desktop, reducing multiple source connections to one

[jthomson · Accepted solution]

Importing as a folder should work

[rushekkgadde]

Thanks Thomson, will try that