Forum Discussion
Working with multiple excel files as data source
Importing as a folder should work
7 Replies
- 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
Importing as a folder should work
- Thanks Thomson, will try that
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.
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.
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
Hi rushekkgadde,
Here is a blog shows that get data from a SharePoint folder which contains multiple Excel files.
Regards,
Yuliana Gu
