Combine Multiple or All Sheets from an Excel File into a Power BI solution Using Power Query Dynamic

Combining multiple files from one folder is already supported built-in through the graphical interface of Power Query when use you Get Data from Folder. However, if you have one Excel file, with multiple sheets with the same structure but different data, you cannot use that option. There is a very little but useful trick that you can use to get data from multiple sheets dynamically rather than manually one by one from each sheet. This video is about that technique.
Learn more detailed steps, with links to study more, from my blog post here:

[Gvegag]

Thank you. Perhaps one of the best tips I've seen so far for time saving.

[clivepetty]

Thanks for sharing the very useful feature, I often have to consolidated multiply data sheets and have used the data append in Power Pivot, which can be some what tedious.

[datamaniac]

what happens if the data in other sheets are not in the same structure?

[TraceyOsbornHR]

You are a lifesaver! Thanks for this information!

[sayantanibandyopadhyay]

Thanks for the video! Great help! Will this also work if I refresh the excel file in the background?

[robertgreen]

Thanks this is cool, but I have several tabs (about 15) that I want to match merge rather than append. Do you know how I could do that? I'm trying to do by pivoting and unpivoting the appended data but I can't figure it out.

[vikassoni]

That was good information. What if I add more rows to one of the excel file after combining them in Power Bi. Will refreshing the data in Power Bi, update the recently added data in excel file?

[chinhnguyennang]

that's what I need now, thank you so much

[dineshtalupulatalupul]

Can we refresh it in Power BI Desktop i mean did the refresh works for the above process in Desktop ?

[ninjablue]

but how do you refresh the date once you change the own sheets like add new culomm?

[shubhabratadey]

Thanks for sharing such a simple yet a powerful solution for us....😊

[bgtjekw]

Great Video thanks for teaching valuable stuff <3

[lewi]

Is there a way to do this for sharepoint files ?

[GurpreetSingh-lfwm]

Pls pls pls reply, i need to combine data from different excel sheets i have 56 excel sheets for 1st 6 months and 56 for another 6 months, how can i combine them in one . Total i have 112 excel sheets

[priteshpatel]

Great video...and this seems rather easy to do, but the assumption here is that no new sheets will be added. How do I do the same if new sheets get added?

[nupurawalunjkar]

what if the columns are not same...???

[clipstheonly]

But you only included one of the sheets in powerBI. I need all the sheets

[turalhuseynov]

Thanks a lot Reza bhai, it helped me a lot.