cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Working with multiple excel files as data source

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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Importing as a folder should work

View solution in original post

7 REPLIES 7
Highlighted
New Member

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
Highlighted

Importing as a folder should work

View solution in original post

Highlighted

Thanks Thomson, will try that
Highlighted
Frequent Visitor

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.

Highlighted
Frequent Visitor

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.

 

 

Highlighted
Super User I
Super User I

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Highlighted
Microsoft
Microsoft

Hi @rushekkgadde,

 

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

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors