@@GoodlyChandeep@chandoo, thank you for all your contents on your blogs and your RUclips channels. You both deserve the blessings and success because your hard work and dedication really help a lot of people in ways too many to imagine.
The last trick is so damn fantastic! It looks so simple yet I wouldn’t ever thought about doing that approach. You made a great explanation on how records works, thank you!
Incredible! I've wondered how to reference intermediate steps in other queries for a long time, nice to see this way of doing it! Just ran across your channel, and love your explanation style. Looking forward to digging through your old videos!
I fell off my seat with the last trick. Amazing. It shows the power of the M language and how it can provide wrappers for just about anything. Thanks Chandeep. Great job!
Great !!! Sir, I was looking for such a Solution where we can access an already worked out process and avoid duplication and bunch of M Code in another query. This way we know exactly our logic is Many Many Thanks to your Sir Khalid Khan
I love the last trick as a resource saving trick as well for two reasons. First, the flexibility. In one case I referenced the same table multiple times for different reasons and so they did not need to load all the applied steps every time. Being able to jump in at the first or second step saves so much confusion in the new tables because the unneeded steps are not there. Second, when I refresh my data model, I noticed that the record refreshes very quickly since it is not actually unpacking the tables as a referenced table or duplicated table would. Instead, it loads the rows of instructions and unpack the table when it called as a source. Saving our team an entire table worth of time, memory, bandwidth for each table we use this technique and every time we refresh our models. It also helps keep the file size down if you need to use this trick multiple times in your data set. That was very important to us before the Query Folding pipelines were available.
Thanks Chandeep - third trick is awesome. This shows how to reference the query steps/records you've created at the beginning of a new query (as the source). But instead, how would you refer it in the middle of the new query (as a single value to be used in a calculation or Custom column)?
This approach with Records is so cool. It can be helpful to avoid circular references. When you need to pick up an early step, manipulate it and after that add it to the final step of the Query A. This is awesome😊
the last trick is really incredible, well done. but my question is what happens when you have additional information in the source file? if you refresh, will the new record pick the new information?
This is amazing trick and I can see how I will use it in my work. Thank you Goodly. I start watching every lesson you have in the channel. Very impressive and really appreciate your work for teaching Power BI techniques.
You definitely are a champion Chandeep! I was litteraly pointing up just when you were explaining the transformation of steps in a record. It is just fabulous. I will rebuild some of my complex power queries to make them lighter. Great great great !
Instead of creating a new query, entering in the name of your main query, and then clicking a table cell in there... In your main query, click the white space to the right of the Table link and choose "Add as new Query". Note that doing this will copy the main query meaning any chances you make to the main query will not be reflected in your new query. Alternatively, you can right click a query and choose reference, which is the same as creating a new blank query and entering the name of another one.
Cant wait to jumble up my queries😂. Amazing last trick! I reference queries a lot and split them as your trick #2, but had never seen that third one. Thank you.
All the 3 tricks are super. Especially the 3rd trick to create new source from existing step of a query. In the 2nd trick you shared reg extract previous to make separate query is also awesome. But it has a limitation I feel, you can't add further steps in the detail table since it becomes source for the original table. Nicely explained and superb recording
Amazing. Question: Many of us have experienced the desire to create trees of dependent queries thinking that we were being very efficient by limiting the number of retrievals from sources, only to discover that actually we made it inefficient because source "trunks" in the tree are re-evaluated each time a child "branch" is evaluated. Now I'm wondering if writing the query as a record and using the ability to grab specific steps for other queries bypasses this problem or is it still an issue in this methodology. My suspicion is that it's the same but given how different this approach is I figure it's worth asking.
Hi, your videos are very interesting and I often learn new stuff but stop constantly teasing on what comes next, we are here for one reason and we stay with you 😉. Vincent from Paris
I doubt that converting the steps into records would slow them down. It should not impact the speed unless there is a problem with the step itself. I haven't tested it thoroughly with very large data. With a couple of million rows it works fine!
Simply brilliant Chandeep! can you guide here - I tried the record trick to source Share Point path and refer it in subsequent queries. The files when updated at the Share Pont does not update here in Power Query. When I change the source step back to table..it does update
Hi, thanks! Will the third query also be able to calculate the result faster since it is only referencing the already calculated results (converted to records)?
Hey Chandeep! Thanks for this video. The third trick is absolutely mind blowing! I was wondering how does this affect query performance. Will it slow my query down?
hey Chandeep, high appreciate this trick! i helped my wife with her analysis on a survey which has many parts to be individually analyzed. i used reference or duplicate but found the query quickly turned into giant mess. this is very helpful to pick up the step i need
For trick #03, export the query steps, how to make this dynamic? I mean, could it be linked somehow to the original query to reflect any change made there?
I think he third trick is really crazy🤭🤭🤭🤭🤭 but I am still trying to pick up good use cases for the trick I hope you make a video with some examples on how to gain from the third trick🤟🤟🤟
3rd trick is awesome..but I am bit worried about the extra load excel will keep on memory to maintain those table records..what's your view on this. Also, can we make particular steps as record
Leveraging already generated steps instead of duplicating queries should actually lighten the load by a lot. The moment you start pulling that data into tables in Excel is when things start to slow down.
OK, I just thought of something: I have this query where I look up the help on all the built-in functions, and then I subsequently I filter that “source” step for bunch of keywords like “Table”, “List”,… So, now you know that in the very near future I will have the capability to ask for Help[Table] anywhere and get exactly what I want… ;-)
Nice tricks. Thanks for sharing. How did you move lines of codes while doing first trick without selecting and copy/pasting? It looked like you used some combination of hot keys or another, 4th trick :)
Any idea how much referencing the query saves as far as time? Also, i'm an Excel/Data snob and tend to think most power bi, vba and Excel videos are lame (except mine). However this channel is great!
For 3rd trick - what if you need to edit or add a step after you turned the table to a record? Revert back to a table and make the transformation then convert to a record again?
Does referencing saves query loading time ? As it starts from intermediate steps or it loads twice? Once for original and next for new referenced query?
Hi Chandeep. Please, recommend books about M formula language syntaxis (how to create functions, understanding scope, recursion, and things like that). I am looking for books about it but can´t find anything. Microsoft documentation is never enough. Thank you for your videos!
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
That is RECORD Breaking...Awesome tricks Chandeep 🤩
Glad to see you here Chandoo. Your puns never cease to amuse me!
Thank you 😊
Nice to see Two Stars on one page 🙏👍👍
omg chandoo you're a legend. I remember using your excel tutorials a very long time ago!
@@GoodlyChandeep@chandoo, thank you for all your contents on your blogs and your RUclips channels. You both deserve the blessings and success because your hard work and dedication really help a lot of people in ways too many to imagine.
The last trick is so damn fantastic! It looks so simple yet I wouldn’t ever thought about doing that approach. You made a great explanation on how records works, thank you!
Exactly how I felt!
@@williamrhopkins same, absolute game changer
Stunning trick, definitely comes handy in creating complex joins only at a particular stage .. Thank you for sharing Chandeep.
Incredible! I've wondered how to reference intermediate steps in other queries for a long time, nice to see this way of doing it! Just ran across your channel, and love your explanation style. Looking forward to digging through your old videos!
Thank you
Could you please post a video on how to use the last trick practically?
I fell off my seat with the last trick. Amazing. It shows the power of the M language and how it can provide wrappers for just about anything. Thanks Chandeep. Great job!
Great to hear!
I fixed my whole life with trick on 6:00. And I did indeed jumped off my chair at 11:00. It's so simple yet powerful.
Great !!! Sir,
I was looking for such a Solution where we can access an already worked out process and avoid duplication and bunch of M Code in another query. This way we know exactly our logic is
Many Many Thanks to your Sir
Khalid Khan
That's fun way to transform steps into a record! I'm liking all this, and it's easy to implement, and to revert. Awesome!
Thanks Rick!
#powerbinareal
Mind. Blown. Super helpful for understanding PQ in more depth. Thank you!!
I love the last trick as a resource saving trick as well for two reasons. First, the flexibility. In one case I referenced the same table multiple times for different reasons and so they did not need to load all the applied steps every time. Being able to jump in at the first or second step saves so much confusion in the new tables because the unneeded steps are not there.
Second, when I refresh my data model, I noticed that the record refreshes very quickly since it is not actually unpacking the tables as a referenced table or duplicated table would. Instead, it loads the rows of instructions and unpack the table when it called as a source. Saving our team an entire table worth of time, memory, bandwidth for each table we use this technique and every time we refresh our models. It also helps keep the file size down if you need to use this trick multiple times in your data set. That was very important to us before the Query Folding pipelines were available.
Thanks Chandeep - third trick is awesome. This shows how to reference the query steps/records you've created at the beginning of a new query (as the source). But instead, how would you refer it in the middle of the new query (as a single value to be used in a calculation or Custom column)?
🤯 This is a game changer! This may change how I will design my queries moving forward...
This approach with Records is so cool. It can be helpful to avoid circular references. When you need to pick up an early step, manipulate it and after that add it to the final step of the Query A. This is awesome😊
Glad you like this Willian!
the last trick is really incredible, well done. but my question is what happens when you have additional information in the source file? if you refresh, will the new record pick the new information?
Yes in deed wonderful. This is what exactly shows up when we connect to sharepoint list and see the site contents
This is amazing trick and I can see how I will use it in my work. Thank you Goodly. I start watching every lesson you have in the channel. Very impressive and really appreciate your work for teaching Power BI techniques.
Thank you, John!
You definitely are a champion Chandeep! I was litteraly pointing up just when you were explaining the transformation of steps in a record. It is just fabulous. I will rebuild some of my complex power queries to make them lighter. Great great great !
Thanks Celine!
Excellent content. Thoughtful and enthusiastic delivery. Thank you sir!
Trick 3 was really a surprise! Really nice mate, keep going with the nice videos! Appreaciate the knowledge sharing!
No doubt - ABSOLUTELTY MINDBLOWING!!! The last one obviously amazing from the utility point of view. Thanks for sharing this.
Great! Thanks Chandeep. Thumbs up!!
Instead of creating a new query, entering in the name of your main query, and then clicking a table cell in there... In your main query, click the white space to the right of the Table link and choose "Add as new Query". Note that doing this will copy the main query meaning any chances you make to the main query will not be reflected in your new query. Alternatively, you can right click a query and choose reference, which is the same as creating a new blank query and entering the name of another one.
Wow Wow Wow Wow You have solved my one problem by using the last technique I can dynamically execute or not to execute the query. Awsome trick
Cool! 💚
This is a great trick!! Saves so much time and make the query so efficient! Thanks
Glad it was helpful Diana!
Cant wait to jumble up my queries😂. Amazing last trick! I reference queries a lot and split them as your trick #2, but had never seen that third one. Thank you.
First trick was nice, but third is mind blowing. Question is impact on performance in complex queries. Would have to check it ;)
Number 3 man!! Woot Woot!!!
it seems so useful bu we also should check the performance of the queries
All the 3 tricks are super. Especially the 3rd trick to create new source from existing step of a query.
In the 2nd trick you shared reg extract previous to make separate query is also awesome. But it has a limitation I feel, you can't add further steps in the detail table since it becomes source for the original table.
Nicely explained and superb recording
Another excellent presentation! Cool to see Chandoo drop in with kudos as well.
Thank you! 💚
the last one is mind blowing. this solves many problems. thank you mate 🙏
I really liked 3rd trick. Thank you Chandeep. You are awesome. lots of love.
Thank you so much 😀
Thanks ... it was really helpful! Best video so far!
Amazing.
Question: Many of us have experienced the desire to create trees of dependent queries thinking that we were being very efficient by limiting the number of retrievals from sources, only to discover that actually we made it inefficient because source "trunks" in the tree are re-evaluated each time a child "branch" is evaluated.
Now I'm wondering if writing the query as a record and using the ability to grab specific steps for other queries bypasses this problem or is it still an issue in this methodology. My suspicion is that it's the same but given how different this approach is I figure it's worth asking.
It'd be great to do a video on a case study where trick number 3 was a useful solution to a problem.
@GoodlyChandeep, any thoughts on my comments here? Seriously wondering about efficiency and would love a good case study.
Thanks.
Hi, your videos are very interesting and I often learn new stuff but stop constantly teasing on what comes next, we are here for one reason and we stay with you 😉. Vincent from Paris
This is awesome!!! Just a quick quesiton on the third method. Would doing that when you have so many steps with a large dataset slowdown Power BI?
I doubt that converting the steps into records would slow them down. It should not impact the speed unless there is a problem with the step itself.
I haven't tested it thoroughly with very large data. With a couple of million rows it works fine!
Love the 3rd trick! 😀👍
2nd trick looks similar to the 'reference' feature, but in reverse 🙂
Simply brilliant Chandeep! can you guide here - I tried the record trick to source Share Point path and refer it in subsequent queries. The files when updated at the Share Pont does not update here in Power Query. When I change the source step back to table..it does update
Now I came to know, no one else can teache with so simple logics power query and BI without any additional fee
Hi, thanks!
Will the third query also be able to calculate the result faster since it is only referencing the already calculated results (converted to records)?
Last trick is awesome. Thank you!
Amazing Trick as usual, Thanks a lot. Cheers
Excellent tricks Chandeep..mind blowing
Thanks!
Mind blowing👍👍👍👍
Hey Chandeep! Thanks for this video. The third trick is absolutely mind blowing!
I was wondering how does this affect query performance. Will it slow my query down?
Not unless your query is already slow
Awesome, that last Record trick is amazing thanks
Glad you liked it Lucena!
Brilliant. Very well explained. Thank You 🤯
Wow... Just Amazing. !!!
This did not disappoint. All are brilliant, thank you. Love the outtake at the end
Glad you like it!
Thank you Chandeep, very useful to experiment with and find alternative query building options!
Glad you liked it
Awesome Chandeep Paaji
Sir , I have a table and I want to add lot of manual data with formulas into the same which is being loaded from power query. Please guide me on it.
Boss, it was ultimate, 🙏🏻
I know this is old but then I'm an old guy playing with code - this if fun Chandeep - thanks buddy. 😉
Great tips bro, thanks
Thanks for the video fantastic keep it up 👍
Does the last trick also speeds up the power query run time as well? I am struggling with the run time right now :(
What if I need to use the 1st table in my view? Since it has already been converted to a record, I won't be able to use it
hey Chandeep, high appreciate this trick! i helped my wife with her analysis on a survey which has many parts to be individually analyzed. i used reference or duplicate but found the query quickly turned into giant mess. this is very helpful to pick up the step i need
Great 👍
Excellent, didn't know the last one, very useful!
very helpful and crazy tricks, thanks for your sharing
Glad it was helpful 😊
Geezzz..!!! That was super cool Chandeep..! I was laughing and enjoying for 5 min.. :D
Thank you so much 😀
You laughed and enjoyed.
For trick #03, export the query steps, how to make this dynamic? I mean, could it be linked somehow to the original query to reflect any change made there?
3rd one is the best and fantastic! Thanks for sharing!
Thanks. Cheers
that last one is actually insane and useful
This is... actually magic. Holy sheet.
this is crazy good!!! The 3rd one is fantastic!!
Glad you liked it
Wow. Always amazed. Where or how did you learn this. Mind Blown. Love number 3. Am having the same problem.
Glad you like it!
Wow mind blown 🤯, you are legend, got out of bed just to try the last trick lol 😂
I think he third trick is really crazy🤭🤭🤭🤭🤭 but I am still trying to pick up good use cases for the trick
I hope you make a video with some examples on how to gain from the third trick🤟🤟🤟
so interesting. many thanks
Mind Blown!
Thank you 💚
3rd trick is awesome..but I am bit worried about the extra load excel will keep on memory to maintain those table records..what's your view on this.
Also, can we make particular steps as record
Leveraging already generated steps instead of duplicating queries should actually lighten the load by a lot. The moment you start pulling that data into tables in Excel is when things start to slow down.
Your final trick really is rad! :-)
I think I’ll find a good use for that. Thanks for sharing!
OK, I just thought of something: I have this query where I look up the help on all the built-in functions, and then I subsequently I filter that “source” step for bunch of keywords like “Table”, “List”,…
So, now you know that in the very near future I will have the capability to ask for Help[Table] anywhere and get exactly what I want… ;-)
@@GeertDelmulle I post more relevant uses of this trick soon. One video at a time :)
PS: works like a charm! (of course it does :-)
Nice tricks. Thanks for sharing. How did you move lines of codes while doing first trick without selecting and copy/pasting? It looked like you used some combination of hot keys or another, 4th trick :)
Place the cursor anywhere on any line of Advanced Editor and then use.. Alt Up or Down Arrow!
Great observation by the way 😉
@@GoodlyChandeep It does not work on my computer. I have MS Office 2016. Does it depend on MS Office edition?
@@konstantinchernyshov7984 Just update your Excel. It should work!
Thanks
More pivot table tips and tricks please 😊
“Wackiness Quotient” LOL
Just found your channel, so awesome! Thank you for the wonderful information!!
Any idea how much referencing the query saves as far as time? Also, i'm an Excel/Data snob and tend to think most power bi, vba and Excel videos are lame (except mine). However this channel is great!
For 3rd trick - what if you need to edit or add a step after you turned the table to a record? Revert back to a table and make the transformation then convert to a record again?
Thanks for sharing its really blowing my minds
Glad you enjoyed it
That third trick goes straight into my utility belt 😎 Thanks, Chandeep!
Glad you found it helpful Eddie!
always Best👌
I learned 3 new things today. thanks :D
Loved the 3rd Trick
Thanks 💚💚
great stuff. i'm definitely gonna use it to prank some workmates. 😄
Haha..cool! 🙃
Trick 3 was really a surprise!
Thanks 💚
Hello. Would be interesting to see how to bulk merge queries (not combine but merging). Really time consuming.
So cool, thanks!
Glad you liked it!
Great video mate
Hi Chandeep does the first trick speed up the query ? With less steps in the UI ?
At around 1:36 how did the steps move without copy paste?
Does referencing saves query loading time ? As it starts from intermediate steps or it loads twice? Once for original and next for new referenced query?
My understanding says.. it would save time.
Nice tricks!👏Thanks for sharing👍
Hi Chandeep. Please, recommend books about M formula language syntaxis (how to create functions, understanding scope, recursion, and things like that). I am looking for books about it but can´t find anything. Microsoft documentation is never enough. Thank you for your videos!
Read the official M documentation from Microsoft
@@GoodlyChandeep I did. But not enough to understand functions properly :/
Thank you!!
Record indeed. Chandoo has spoken, what more can I say
Many thanks! Crazy...
Muito top!!!!!!!!! Very cool!
I hope one day to exchange many ideas with you! #powerbinareal
is it going to slow down data processing?