Convert Your Query into an M Function | Combine Data from Multiple Excel Sheets in Power BI

[@filipwinski4219]

Great Video !!! I've been working with Power Query for a long time, but this is the first time I really understand what a custom function is for. Thank you Bas !!!

[@HowtoPowerBI]

Thanks Filip! 😀 custom functions are definitely worth looking further into, opens up a lot of new possibilities

[@atruewild]

I work with Power Query ever day to design data transformations to support business needs. This is the most impactful video I have watched! Thank you! You are a great teacher. Amazing content.

[@anuppatil4444]

Amazing.. everytime I seem to learn something new watching your videos

[@HowtoPowerBI]

So nice of you, thx!🙂

[@huser365]

Next to the good explanations, tips, and tricks, I just love your enthusiasm and dynamism. Thx Bas.

[@HowtoPowerBI]

Thats so nice! Thanks a lot Serge, positive vibes onlyyyy :):)

[@aalectures8576]

Greatest video very interesting and helpful Thank you so much❤

[@joaorataoo]

Very good video! I never thought of using the steps saved in a process within a query as a function. Very smart!!!

[@HowtoPowerBI]

makes your life a lot easier when working in PQ - thx João 😀

[@SanketArora]

Very insightful video, Bas. I was looking for this workaround for sometime now. Great content! Your videos are very informative and easy to understand. Thanks!

[@HowtoPowerBI]

Great to hear you find it helpful and easy to understand Sanket! thank you for your kind words, I appreciate it very much :)

[@yuhooh]

Thanks , your video is exact match I need for my case. I do have excel workbook contain daily data for each worksheet. when create with custom function it help automated combine all daily data from multiple sheet into one month table.

[@ronniehugo]

This is awesome! Very useful so I subscribed.

[@HowtoPowerBI]

thx for the sub Ronnie!🙂

[@xodrinker]

As usual, great content, well explained. Thx.

[@HowtoPowerBI]

thx for continuously watchn my videos :) appreciate it

[@azzamsiddiqui9510]

Very nice. Thanks for explaining it so well.

[@HowtoPowerBI]

thx Azzam!!! 😀

[@firmanwijaya163]

Hi thanks for this amazing tutorial is really useful, could you please explain if the data is in another file not another sheet? thank you

[@TomMattle]

Great videos! Where did you learn all of your Power BI knowledge?

[@HowtoPowerBI]

thanks Tom!!! 😀 .. a lot practice 🙃.. there is a lot of free content (blogs videos) that can help you speed up your learning.

[@NithinJB]

That was super informative!!! Real Time Saver

[@HowtoPowerBI]

thx for watching Nithin! 😀

[@MaanEid]

Amazing and Simple! Thanks a lot

[@HowtoPowerBI]

:):) happy to hear you like it!

[@GoodJob13]

Superb Vid. 👏 I realise it was 2 years ago, but can this be applied some how to Workbooks in a folder?

[@kgautham001]

thanks for sharing the knowledge

[@HowtoPowerBI]

thx for watching!! 🙂

[@workstuff5253]

Fantastic video as usual!! I assume you know this but @3:29 instead of deleting all the steps back to the first one, you can instead right click on the 2nd step and choose "Delete Until End".

[@HowtoPowerBI]

yea that would have been easier 😅 thx!

[@jwatkins320]

I had no clue. That is awesome

[@HowtoPowerBI]

thanks! 😀

[@cristianq.1655]

thanks!! A M A Z I N G !!!!

[@PedroCabraldaCamara]

that is so cool!! ( i use the import from folder and duplicate the file if needed)

[@HowtoPowerBI]

thx Pedro! import from folder generates a function just like the one in this video, but than with file as input parameter instead of a sheet. Depends of course on the specific case what would be the best option.

[@flaeski]

Muito bom! Ótimo vídeo! Parabéns 👏👏👏

[@rawwwr19]

Hello, Bas! Could you tell me please, why we wanna create a custom function to combine data from multiple sheets? What about this solution? Append data from all sheets and then clean & transform. I have a feeling that the first one is better, but I can't explain why.

[@Sheepy0861]

I have the same question Bas? Thanks for all your content, really great stuff.

[@CarmenCeciliaMast]

graciassss!!!

[@tna930930]

Hi, what if instead of excel workbook, my source is from folder. Contains multiple workbooks. Initially the workbook only contains single sheet. But recently, a new sheet is added and when i refresh, power query didn’t extract the new one. How and where do i have to change the code? Thank you

[@davidjosevarelagarcia7011]

Great Video, just a question, what is the different between put in ( sheet as table) the name of the funtion and add "as table"? you dont do it, but in others tutorial i have saw it. even after the () put as table , eg. ( sheet as table) as table =>. what the different?

[@julie_chen]

This is Bananas. Saved it for future use,😊

[@safiyadevarshi839]

How does this work if we keep adding new sheet each year? Could you please help us with a video on how to combine different workbooks stored on a SharePoint folder. Plzzzzzz

[@danielosorio217]

How would it be the process to combine data from multiple Excel tabs contained in a single Excel file and have Power BI automatically refresh visuals that are housed in a single PBI tab? Power BI will need to retrieve the data from the following month's data tab created in the Excel data source. The spreadsheet has data for each month, so it would be great to have either Power BI create a new tab/report for the new month added to the Excel report by recycling the same visuals or change all visuals within the same Power BI report tab, switching all visuals to retrieve data from the next current month added to the new Excel tab. Caveat: some visuals use calculations that create a report for the last 7 days, so if today is May 3, we still still need the data from the last 4 days of April. What methods could achieve this task? Thanks!

[@abubakrsaadmakyhoun7535]

Big like

[@HowtoPowerBI]

thx for your continuous support by watching my videos! 😀

[@thamizhansudip6644]

Good video but whats the use bro ?? You can't refresh the data if a pbix uses custom function to dynamically accept the folder name on cloud. When I remove custom function I was able to refresh the data. But with custom function to accept the folder name dynamically any optionis avl

[@HowtoPowerBI]

Thanks Thamizhan! the custom function in the video cleans up the data on an excel sheet - it doesn't dynamically accept the folder name on cloud.

[@muralikrishna8392]

I want to calculate weekdays between two different dates with multiple condition like, For. Approverd different start and different end Date For hold different start and end date.. All are in same table. I need help.

[@HowtoPowerBI]

you could either calculate it with DAX or in power query. For the second option I have a video here that gives you a starting point kzbin.info/www/bejne/m2PMlGdpZ7Opnrs

[@muralikrishna8392]

@@HowtoPowerBI That helped me a lot but my query is quiet different. For Approval different start and enddate are there For hold different start and enddate are there..... All in one table.

[@muralikrishna8392]

I cannot use that querey for diierent start date and enddate. In a same table where multiple start date and enddates are there in a same table.

[@aida126atc]

ogggg god I love you