No video :(

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

Подписаться 71 тыс.

Просмотров 68 тыс.

50% 1

Hey guys! Here's another awesome video from Reid Havens over at the Havens Consulting channel! Check out this sweet trick and learn how to utilize Power Query’s folder combine data source to connect to a folder of Excel files, each containing multiple sheets that need to be combined.
Link to Reid's Channel!
/ @havensconsulting
To enroll in my introductory or advanced Power BI courses:
training.bielite.com/
Connect with me on Twitter!
/ powerbielite

Опубликовано:

6 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 54

@atanasprodatanasov9233 4 года назад

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

@afomachukwuevanokafor9694 2 года назад

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

@comebakqueen 2 года назад

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

@decentmendreams 2 года назад

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

@dn9361 Год назад

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

@Akirmax 4 года назад

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

@BIElite 4 года назад

That's what this channel is about! :)

@ninabonita8 Год назад

Very Helpful!! Thank You!

@pegahelahi8913 4 года назад

This was great! Thanks.

@jraj2380 Год назад

Very informative. Thank you.

@Menegueli027 4 года назад

Awesome this explanation, works great, thank u.

@cleanermail8816 5 лет назад

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

@BIElite 5 лет назад

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

@HavensConsulting 5 лет назад

Thanks Clea!

@Dominicanamente 3 года назад

Thank you very much!

@JonathanCHANAL 5 лет назад

You are awesome ! Thank you very much ;)

@Blackdocks 4 года назад

WoW really good explanation, you nailed it !

@BIElite 4 года назад

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

@nehashahpatel1741 3 года назад

Million thanks

@bigdj0ntz8 4 года назад

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 2 года назад

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 4 года назад

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?

@deepakgupta3192 4 года назад

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

@myazigi 5 лет назад

Superb!!! Thank you.

@BIElite 5 лет назад

Great trick indeed!

@HavensConsulting 5 лет назад

Thanks Mauricio!

@sssanalytics9532 3 года назад

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?

@cariday4501 4 года назад

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"?

@rajeevverma7235 3 года назад

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

@matheuschristofflauria4143 4 года назад

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?

@user-ze4xc6tu4f 2 года назад

awesome!

@RJYL 4 года назад

Great job

@BIElite 4 года назад

Indeed, this is a great trick from Reid!

@samilkanthwar 3 года назад

Can we do merge instead of append for the same problem

@atanasprodatanasov9233 3 года назад

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?

@deepakgupta3192 5 лет назад

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

@andresjustinianod.7067 2 года назад

Thanks

@maseratination 3 года назад

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?

@dinethprabash1001 4 года назад

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

@bhavinparsana1165 4 года назад

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.

@bleepmaster23 3 года назад

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

@sandip_bettereveryday 5 лет назад

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

@HavensConsulting 5 лет назад

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 RU-vid comments, but please feel free to reach out to me directly if you have further questions. reid@havensconsulting.net

@MacPaulos 5 лет назад

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 5 лет назад

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

@MacPaulos 5 лет назад

@@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 5 лет назад

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 5 лет назад

@@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.

@nobogurus 2 года назад

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

@nishantkumar3865 5 лет назад

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 5 лет назад

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.