Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tina_belcher123
New Member

Combine multiple files from SharePoint folder using Power BI query

Hello All!

 

I've been stuck on this one for a while now and I'm hoping to get some help.

 

I have a folder on SharePoint containing 3 Excel files. All files have the exact same formatting but one has 701 rows of data while the other two each have 3 rows of data. My goal is to simply combine data from all files into Power BI using the Combine Files feature. I am able to connect to the file destination, and transform the data to select the 3 files, giving me the following view:

tina_belcher123_0-1717789556818.png

However, when selecting the "Combine Files" function (no matter what file I choose as the sample file) both files containing only 3 rows of data end up with multiple rows of null data added to them as shown below.

tina_belcher123_1-1717790608246.png

It seems like the query editor is trying to make all files uniform with the one file that contains 701 rows of data. This doesn't happen when I load the data, combine, and then transform it, however, that method is not ideal in this scenario as I must transform the data first to filter only the files in the destination folder.

Any help is appreciated! Please let me know if there is anything I can clarify.

1 ACCEPTED SOLUTION

Thanks for the shout out @foodd 

 

@tina_belcher123 it seems yiur files with 3 rows don't actually only have 3 rows but have lots of empty rows - which look blank.

To check that theory you can open

one of the 3 row files click on row 4, hold shift and press down arrow then delete all rows and save. Then in Power Query that file will not show the nulls.


to avoid having to do that step just choose remove rows- empty rows in power query.

 

 

for the best techniques you can check out the 3rd and 4thvideo in my SharePoint playlist here Power Query and SharePoint / OneDrive

https://www.youtube.com/playlist?list=PLlHDyf8d156W_I_ycA7kbfLKAej54p9Un

MVP | Author of Power BI for the Excel Analyst | Speaker | Power BI & Excel Developer & Instructor | YouTube- 5 million views | Fan of Power Query & XLOOKUP | Purpose: Making life easier for people through applying and sharing knowledge.

View solution in original post

4 REPLIES 4

Thanks for the shout out @foodd 

 

@tina_belcher123 it seems yiur files with 3 rows don't actually only have 3 rows but have lots of empty rows - which look blank.

To check that theory you can open

one of the 3 row files click on row 4, hold shift and press down arrow then delete all rows and save. Then in Power Query that file will not show the nulls.


to avoid having to do that step just choose remove rows- empty rows in power query.

 

 

for the best techniques you can check out the 3rd and 4thvideo in my SharePoint playlist here Power Query and SharePoint / OneDrive

https://www.youtube.com/playlist?list=PLlHDyf8d156W_I_ycA7kbfLKAej54p9Un

MVP | Author of Power BI for the Excel Analyst | Speaker | Power BI & Excel Developer & Instructor | YouTube- 5 million views | Fan of Power Query & XLOOKUP | Purpose: Making life easier for people through applying and sharing knowledge.

Hello @wynhopkins!

Thank you for your help! Turns out I had a few formatting issues, one being the null rows weren't truly blank like you mentioned and the sheet names were not uniform. Thanks to your videos I was able to sort all the errors out. I will definitely be returning to your tutorials in the future, such fantastic resources! Kudos to @foodd for the recommendation!

😃Glad to help

MVP | Author of Power BI for the Excel Analyst | Speaker | Power BI & Excel Developer & Instructor | YouTube- 5 million views | Fan of Power Query & XLOOKUP | Purpose: Making life easier for people through applying and sharing knowledge.
foodd
Super User
Super User

Hello @tina_belcher123 , and thank you for starting a conversation on the Power BI Desktop Forum.  @wynhopkins  has produced a number of ready to use trainings and power query which address the task successfully and with options.   Search on the Microsoft Forums, LinkedIn, his website, and his Youtube Channel for this and other helpful guidance.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors