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

Рет қаралды 15,880

Күн бұрын

In this video I show you how to turn a query into a custom function in Power BI. As an example I combine data from different Excel sheets by reusing a previously built query.
Learn more about power query and how to use M in Power Bi.
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
0:00 Intro
1:18 Building the query we turn into a function
3:06 Converting a query into a function
5:29 Invoking the function
08:07 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on KZfaq: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI #PowerBI #DataTraining
#powerbidesktop #powerbitraining #powerbideveloper #DAX

Пікірлер: 53

@filipwinski4219 3 жыл бұрын

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 3 жыл бұрын

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 3 жыл бұрын

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

@HowtoPowerBI 3 жыл бұрын

So nice of you, thx!🙂

@joaorataoo 2 жыл бұрын

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

@HowtoPowerBI 2 жыл бұрын

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

@SanketArora 2 жыл бұрын

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 2 жыл бұрын

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

@huser365 2 жыл бұрын

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 :):)

@ronniehugo 3 жыл бұрын

This is awesome! Very useful so I subscribed.

@HowtoPowerBI 3 жыл бұрын

thx for the sub Ronnie!🙂

@yuhooh 4 ай бұрын

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.

@xodrinker 3 жыл бұрын

As usual, great content, well explained. Thx.

@HowtoPowerBI 3 жыл бұрын

thx for continuously watchn my videos :) appreciate it

@flaeski 2 жыл бұрын

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

@azzamsiddiqui9510 3 жыл бұрын

Very nice. Thanks for explaining it so well.

@HowtoPowerBI 3 жыл бұрын

thx Azzam!!! 😀

@MaanEid 2 жыл бұрын

Amazing and Simple! Thanks a lot

@HowtoPowerBI 2 жыл бұрын

:):) happy to hear you like it!

@NithinJB 2 жыл бұрын

That was super informative!!! Real Time Saver

@HowtoPowerBI 2 жыл бұрын

thx for watching Nithin! 😀

@kgautham001 3 жыл бұрын

thanks for sharing the knowledge

@HowtoPowerBI 3 жыл бұрын

thx for watching!! 🙂

@jwatkins320 3 жыл бұрын

I had no clue. That is awesome

@HowtoPowerBI 3 жыл бұрын

thanks! 😀

@cristianq.1655 2 жыл бұрын

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

@PedroCabraldaCamara 3 жыл бұрын

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

@HowtoPowerBI 3 жыл бұрын

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.

@TomMattle 3 жыл бұрын

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

@HowtoPowerBI 3 жыл бұрын

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

@GoodJob13 Жыл бұрын

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

@workstuff5253 3 жыл бұрын

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 3 жыл бұрын

yea that would have been easier 😅 thx!

@rawwwr19 2 жыл бұрын

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.

@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

@CarmenCeciliaMast 9 күн бұрын

graciassss!!!

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

@tna930930 2 жыл бұрын

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

@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

@abubakrsaadmakyhoun7535 3 жыл бұрын

Big like

@HowtoPowerBI 3 жыл бұрын

thx for your continuous support by watching my videos! 😀

@danielosorio217 2 ай бұрын

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!

@aida126atc 8 ай бұрын

ogggg god I love you

@thamizhansudip6644 2 жыл бұрын

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 2 жыл бұрын

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 3 жыл бұрын

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 3 жыл бұрын

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 kzfaq.info/get/bejne/m5iZlpdkl7TamI0.html

@muralikrishna8392 3 жыл бұрын

@@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 3 жыл бұрын

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.