Power BI - Combining Multiple Sheets From Multiple Files in Power Query

[@atanasprodatanasov9233]

That's the best explanation on the internet! Not to mention it saves tons of manual work! amazing!!!!!!

[@samilkanthwar]

Can we do merge instead of append for the same problem

[@Akirmax]

Finally a useful video which explains the non-obvious stuff! GREAT

[@BIElite]

That's what this channel is about! :)

[@matheuschristofflauria4143]

Nice! However, what if I have a folder in which there will be files being uploaded on a monthly basis? Those files are excel files with multiple sheets. I need two sheets from each excel file and they are standardized. So I want to do one query to edit each sheet from each file and then combine them, every month there will be more files that will have to be merged to previous data. Anyone?

[@cleanermail8816]

Very useful! Thanks! And I like seeing collaboration between Reid and Parker.

[@BIElite]

It's been fun working with Reid! Glad you're liking the collaboration.

[@HavensConsulting]

Thanks Clea!

[@afomachukwuevanokafor9694]

Finally i got a video clearly explaining this. Thank you so much... I replicated this on my own even up to 13 sheets with big data

[@decentmendreams]

I searched for this video for an hour. I thought it was under his page. I really like this technique.

[@comebakqueen]

THIS IS THE KZbin I'VE BEEN LOOKING FOR! Thanks Yoda; I was bamboozled on this one for a while; you've absolutely made my day =).

[@bigdj0ntz8]

great explanation, works fine, thank u! :) I have an issue, when I add workbooks to the folder, there's some rows duplicate that I want to delete, I tried with "remove duplicate rows" in the sample file but its now working, I want that every time I add a file to the folder and refresh data, remove all duplicate rows, how can I fix this? thanks in advance

[@jordanfalcon]

Hi - thank you for this, as this is really helpful! I've got a question in regards on automation. If I want to connect PowerBI to a folder (data source), where it picks up the latest as the data is provided on a weekly basis in Excel file format. How do I go about doing this?

[@methmabandara9338]

Very useful one. Does this way which is doing transformations for individual sheet has a performance impact when the number of sheets are more than 2 and data is large?

[@dn9361]

You are the man! really appreciated simple stuff but difficult if you dont know those steps ..thank you

[@Dominicanamente]

Thank you very much!

[@rajeevverma7235]

Hi Reed this is very helpful, does this create saparate data model for each.

[@deepakgupta3192]

Great explanation friend . Ur awesome , please make vedio on multiple relationship, please add when we use multiple relationship in power BI . Please please please

[@maseratination]

How would you be able to separate the Tables if you wanted to so you have one for Canada and another for the US instead of merging them?

[@sandip_bettereveryday]

How to avoid hard coding sheet names? (If source sheet names keep on changing, but sequence remain same)

[@HavensConsulting]

Hi Sandip, there is a way to combine every single sheet in each file. But typically a static named table or sheet is what is looked for when combining from folder. Might be too long of a conversation to have via KZbin comments, but please feel free to reach out to me directly if you have further questions. [e-mail]

[@dinethprabash1001]

what if I have 190+ countries in each excel file as sheets and 100+ years as different excel files?

[@ninabonita8]

Very Helpful!! Thank You!

[@jraj2380]

Very informative. Thank you.

[@atanasprodatanasov9233]

One Question. What if somewhere in the future my files have additional tab with new data which i want to include as well? How do I add only this new tab?

[@sssanalytics9532]

Great job! I have got a question, some of my files has second sheet, other doesn't and it is creates an error, is there any solution for that?

[@bhavinparsana1165]

Transform Sample File shown only first file tables. When Number of Columns are different in both files than in second file Tables we can not apply Transform Sample File for add or remove of columns. Any Solution for this...Its use full for more cleanup data.

[@cariday4501]

If you have a 3rd or 4th sheet that you are combining, do you always refer to the "Source US" as the 'Insert After Step - Source'? or should the third source refer to the "Source Canada"?

[@JonathanCHANAL]

You are awesome ! Thank you very much ;)

[@bleepmaster23]

I was wondering do you know what to do when there is a hidden column?

[@deepakgupta3192]

Hi everyone is working on same type of sheet, I have two excel files or more than two excel files each one have different set of data, I am building a report where I use all these files . I don't want to append . So I want next month when I move my excel files it should update automatic . Is this the limitation in power bi ?? Please answer thanks

[@pegahelahi8913]

This was great! Thanks.

[@nobogurus]

How do i combine multiple excel files with multiple sheets each sheet have different data structure? Each file has 6 sheets.

[@Blackdocks]

WoW really good explanation, you nailed it !

[@BIElite]

+Blackdocks Thanks for watching! I’ll let Reid know of your comment

[@MacPaulos]

Very nice. Would love a multiple google sheets conversion (to XLSX or CSV....anything but google due to PBI not reading) video...if possible. Extra challenge...without google API (client restricted)

[@BIElite]

+Paul MacLennan Hey thanks for bringing up this suggestion! So Power BI isn’t reading the sheets correctly? I’ll take a look!

[@MacPaulos]

@@BIElite - I haven't seen anyone crack this without needing a long winded process of getting an Ouath2 via API or very vague explanations of implementing R. Big thanks for even replying...please keep up the vids, really makes my morning everytime

[@BIElite]

Hey Paul, I've looked into this a bit in the past few days. The only solution I've found so far involves publishing the Google Sheet to Web and then using the access link. It works perfectly, but I understand that this won't be acceptable for some projects due to security concerns. Do you need to keep your data more secure than this?

[@MacPaulos]

@@BIElite - Thanks for checking. I do need it more secure than public publish. No worries, I figured if there was a simple workaround then it would be advertised heavily on the internet. Really appreciate the time taken and ALL of the content on your channel.

[@nishantkumar3865]

I have my data in MongoDB(on the cloud) and I have created a report from that. I want it to update automatically without gateway(no need of a laptop to be running at that time like in Azure SQL Database). So What step I should follow. Please guide me. Currently, I am Using R script for data so it required a local gateway.

[@BIElite]

Hi Nishant, if you are using an R script, I think you have to use the gateway. Can you try to connect to MongoDB via ODBC? If you can, you won't need to go through a gateway. In fact, you could set it up as a Dataflow to keep everything in the cloud.

[@Menegueli027]

Awesome this explanation, works great, thank u.

[@RJYL]

Great job

[@BIElite]

Indeed, this is a great trick from Reid!

[@myazigi]

Superb!!! Thank you.

[@BIElite]

Great trick indeed!

[@HavensConsulting]

Thanks Mauricio!

[@andresjustinianod.7067]

Thanks

[@nehashahpatel1741]

Million thanks

[@就已]

awesome!

[@JohnnyTwoFingers]

Man, it's only 6 months since you published this and PBI already has more than one changes in the options you choose.

[@BIElite]

+Trevor G sounds like Power BI lol

[@PedroCabraldaCamara]

awesome!