Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
@chandoo_2 жыл бұрын
That is RECORD Breaking...Awesome tricks Chandeep 🤩
@GoodlyChandeep2 жыл бұрын
Glad to see you here Chandoo. Your puns never cease to amuse me! Thank you 😊
@dhananjaypinjan26432 жыл бұрын
Nice to see Two Stars on one page 🙏👍👍
@culpritdesign2 жыл бұрын
omg chandoo you're a legend. I remember using your excel tutorials a very long time ago!
@lar50912 жыл бұрын
Stunning trick, definitely comes handy in creating complex joins only at a particular stage .. Thank you for sharing Chandeep.
@thamilanban2 жыл бұрын
Thank you Could you please post a video on how to use the last trick practically?
@odekunleodebiyiomotayo6690 Жыл бұрын
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?
@HachiAdachi2 жыл бұрын
🤯 This is a game changer! This may change how I will design my queries moving forward...
@yemiakinwande7039 Жыл бұрын
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)?
@buhogris2 жыл бұрын
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!
@williamrhopkins Жыл бұрын
Exactly how I felt!
@phillipkadaoui9649 Жыл бұрын
@@williamrhopkins same, absolute game changer
@double-excel Жыл бұрын
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!
@Paladin1013 ай бұрын
I know this is old but then I'm an old guy playing with code - this if fun Chandeep - thanks buddy. 😉
@subanark2 жыл бұрын
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.
@reanalytics18632 жыл бұрын
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🤟🤟🤟
@wayneedmondson10652 жыл бұрын
Great! Thanks Chandeep. Thumbs up!!
@keagankemp62752 жыл бұрын
Wow mind blown 🤯, you are legend, got out of bed just to try the last trick lol 😂
@powerbinareal Жыл бұрын
Muito top!!!!!!!!! Very cool! I hope one day to exchange many ideas with you! #powerbinareal
@tAnkRolling7 ай бұрын
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?
@GoodlyChandeep7 ай бұрын
Not unless your query is already slow
@scottcha032 жыл бұрын
This did not disappoint. All are brilliant, thank you. Love the outtake at the end
@GoodlyChandeep2 жыл бұрын
Glad you like it!
@thiagofraletti42202 жыл бұрын
Trick 3 was really a surprise! Really nice mate, keep going with the nice videos! Appreaciate the knowledge sharing!
@cherianiype2 жыл бұрын
Number 3 man!! Woot Woot!!!
@konstantinchernyshov79842 жыл бұрын
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 :)
@GoodlyChandeep2 жыл бұрын
Place the cursor anywhere on any line of Advanced Editor and then use.. Alt Up or Down Arrow! Great observation by the way 😉
@konstantinchernyshov79842 жыл бұрын
@@GoodlyChandeep It does not work on my computer. I have MS Office 2016. Does it depend on MS Office edition?
@GoodlyChandeep2 жыл бұрын
@@konstantinchernyshov7984 Just update your Excel. It should work!
@konstantinchernyshov79842 жыл бұрын
Thanks
@McKaySavage2 жыл бұрын
Lol, that first trick was exactly as you said: awesome wacky and useless, but totally good for learning how M code works. I really like how you shook up the usual bland learning video format
@BIGorilla Жыл бұрын
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!
@GoodlyChandeep Жыл бұрын
Thanks Rick!
@powerbinareal Жыл бұрын
#powerbinareal
@garylhaas20052 жыл бұрын
my head is hurting. did you sell your soul to the devil to obtain your level of knowledge of M-code?
@GoodlyChandeep2 жыл бұрын
Haha. No man just had to double mortgage it! 😁 Jokes apart, I have read the M primer series from Ben's Blog bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let and read the official MS M Spec Guide, although a bit drab but it's awesome. docs.microsoft.com/en-us/powerquery-m/opbuildpdf/toc.pdf?branch=live
@gauravsukhadia6382 жыл бұрын
Loved 3rd trick
@GoodlyChandeep2 жыл бұрын
Thanks!
@AnshulMarele-gbaaАй бұрын
While applying with Record system, getting error: "Formula.Firewall: Query 'Query1' (step 'Query1') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."
@udusegbe2 жыл бұрын
Love the 3rd trick! 😀👍 2nd trick looks similar to the 'reference' feature, but in reverse 🙂
@janardhanansgm5212 жыл бұрын
First Like chandoo..👌
@christopherhazel8281 Жыл бұрын
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.
@christopherhazel8281 Жыл бұрын
It'd be great to do a video on a case study where trick number 3 was a useful solution to a problem.
@christopherhazel8281 Жыл бұрын
@GoodlyChandeep, any thoughts on my comments here? Seriously wondering about efficiency and would love a good case study. Thanks.
@vl21i2 жыл бұрын
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
@TheMarked100022 күн бұрын
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.
@JonnyMacVA Жыл бұрын
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!
@GoodlyChandeep Жыл бұрын
Great to hear!
@Magdoulin10 ай бұрын
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?
@learningsessions45847 ай бұрын
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
@TFu-Soco Жыл бұрын
Can you actually reference the step while it is in query form and not converted to a record??? I can't seem to make it work... I am not sure how practical it really is to convert your query to a record just to reference a step....
@cbhang3 ай бұрын
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)?
@julie_chen Жыл бұрын
I’ve saved this into my library folder named Bananas (Guy in Cube’s Phrase).😊
@saching3024 Жыл бұрын
Your screen not properly visible as compared to other power query youtubers.....
@AshishBajoria6 ай бұрын
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.
@its85242 жыл бұрын
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
@gabriusv2 жыл бұрын
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.
@ryanm803 Жыл бұрын
[7:08]... I'll be able to do WHAT after I learn how records work?!?! 😂
@shubhabratadeyАй бұрын
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
@UU-ry6gt Жыл бұрын
Great! Only one challenge here: it doesn't work in ServiceBI
@kimbersoots53652 жыл бұрын
"How do insane people write their queries?" 🤣 I don't know but I can't wait to find out
@sue_bayes2 жыл бұрын
Cool trick. I wonder if there is any performance gains to storing the query as a record and referencing the relevant step
@GoodlyChandeep2 жыл бұрын
Hey Susan, glad to see you here! I am not sure either. May be we can get the benefit of saved preview time since the tables are collapsed. I'll dig deep into this in the coming weeks.
@sue_bayes2 жыл бұрын
@@GoodlyChandeep Love the channel, a friend told me about it and really enjoying the content :)
@haozhang14362 жыл бұрын
Hi Chandeep does the first trick speed up the query ? With less steps in the UI ?
@unboxingcategory33532 жыл бұрын
the last trick gimme an example I will need it until i changed source query into record
@stj5758 Жыл бұрын
Great solution for referencing the intermediate step. However it was giving me error when the Source was not from a table, but was referencing an external file. "Error: Formula.Firewall: Query "query name" references other queries or steps, so it may not directly access a data source. Please rebuild this combination"
@g-movies494Ай бұрын
Can anyone find a solution for this error?
@PranayPawar19916 ай бұрын
Amazing Trick as usual, Thanks a lot. Cheers
@willianthalles Жыл бұрын
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😊
@GoodlyChandeep Жыл бұрын
Glad you like this Willian!
@rajanpradeepankarath88465 ай бұрын
Record indeed. Chandoo has spoken, what more can I say
@shafa76682 жыл бұрын
First trick- has cache memory or AI got to do anything with it? As you have run the query in normal way first , so when you shuffle your steps , it is still processing based on first run. So if you write it on advanced editor in this insane way is it going to work?
@GoodlyChandeep2 жыл бұрын
Yes, for Power Query it doesn't matter whether the steps are in order or not. It always starts to evaluate from the last step. The order was introduced to make it easier to work with the UI.
@dp707372 ай бұрын
At around 1:36 how did the steps move without copy paste?
@txreal2 Жыл бұрын
Insane!
@vigneshahob2 жыл бұрын
Geezzz..!!! That was super cool Chandeep..! I was laughing and enjoying for 5 min.. :D
@GoodlyChandeep2 жыл бұрын
Thank you so much 😀 You laughed and enjoyed.
@srider33 Жыл бұрын
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!
@alexandrus Жыл бұрын
How to reference a query saved in txt file?
@YvesAustin Жыл бұрын
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.
@sanglee9937 Жыл бұрын
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?
@GoodlyChandeep Жыл бұрын
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!
@BrainyBrunetteBarbie2 жыл бұрын
“Wackiness Quotient” LOL Just found your channel, so awesome! Thank you for the wonderful information!!
@KhalidKhans55 Жыл бұрын
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
@saviodcunha28302 жыл бұрын
The last trick is truly WACKY !!!
@bartwy5676 Жыл бұрын
First trick was nice, but third is mind blowing. Question is impact on performance in complex queries. Would have to check it ;)
@jimbeverley11 ай бұрын
Mind. Blown. Super helpful for understanding PQ in more depth. Thank you!!
@Jeena-s7c7 ай бұрын
More pivot table tips and tricks please 😊
@alansnyder656511 ай бұрын
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.
@LimeulVincent Жыл бұрын
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
@Bangloreblogs2 жыл бұрын
Hello chandeep can you refer me some power bi service channels or any references...just like you shared last time of pragmatic works
@thanhphongle73252 ай бұрын
What is the shortcut to move the M code, bro😢
@ianmacnz9045 Жыл бұрын
Excellent content. Thoughtful and enthusiastic delivery. Thank you sir!
@lionels839 Жыл бұрын
Hello. Would be interesting to see how to bulk merge queries (not combine but merging). Really time consuming.
@chengwang4112 жыл бұрын
great stuff. i'm definitely gonna use it to prank some workmates. 😄
@GoodlyChandeep2 жыл бұрын
Haha..cool! 🙃
@PowerMacro2 жыл бұрын
Now I came to know, no one else can teache with so simple logics power query and BI without any additional fee
@Koopzy3 ай бұрын
that last one is actually insane and useful
@jaymehta33202 жыл бұрын
No doubt - ABSOLUTELTY MINDBLOWING!!! The last one obviously amazing from the utility point of view. Thanks for sharing this.
@scoflix7 ай бұрын
Thanks ... it was really helpful! Best video so far!
@MrPaunty Жыл бұрын
is it going to slow down data processing?
@GrainneDuggan_Excel2 жыл бұрын
Wacky, but useful trick Chandeep - Thanks
@rhaps2008 Жыл бұрын
Awesome, that last Record trick is amazing thanks
@GoodlyChandeep Жыл бұрын
Glad you liked it Lucena!
@boominathanaara5599 Жыл бұрын
Yes in deed wonderful. This is what exactly shows up when we connect to sharepoint list and see the site contents
@__HumanBeing2 жыл бұрын
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!
@GoodlyChandeep2 жыл бұрын
Read the official M documentation from Microsoft
@__HumanBeing2 жыл бұрын
@@GoodlyChandeep I did. But not enough to understand functions properly :/ Thank you!!
@alexgoommen2 жыл бұрын
Was going through the courses, MASTER EXCEL Step by Step, all chapters and modules with each video explanations ?
@GoodlyChandeep2 жыл бұрын
Yes all lessons are videos along with downloadable excel files. With exercises in between!
@Sal_A2 жыл бұрын
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?
@RS-we7lx Жыл бұрын
Watching to see if I am insane 😊
@AnshulMarele-gbaaАй бұрын
Boss, it was ultimate, 🙏🏻
@kadirkkkk2 жыл бұрын
it seems so useful bu we also should check the performance of the queries
@webwatcher188 Жыл бұрын
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.
@GoodlyChandeep Жыл бұрын
Thank you, John!
@sybarix4 ай бұрын
Great tips bro, thanks
@damionc Жыл бұрын
Wow. Always amazed. Where or how did you learn this. Mind Blown. Love number 3. Am having the same problem.
@GoodlyChandeep Жыл бұрын
Glad you like it!
@sanmaj1002 жыл бұрын
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
@GoodlyChandeep2 жыл бұрын
Cool! 💚
@nathanhoover51562 жыл бұрын
Excellent, didn't know the last one, very useful!
@celinemadore7059 Жыл бұрын
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 !
@GoodlyChandeep Жыл бұрын
Thanks Celine!
@GeertDelmulle2 жыл бұрын
Your final trick really is rad! :-) I think I’ll find a good use for that. Thanks for sharing!
@GeertDelmulle2 жыл бұрын
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… ;-)
@GoodlyChandeep2 жыл бұрын
@@GeertDelmulle I post more relevant uses of this trick soon. One video at a time :)
@GeertDelmulle2 жыл бұрын
PS: works like a charm! (of course it does :-)
@iankr5 ай бұрын
Many thanks! Crazy...
@KawanExcel2 жыл бұрын
AS always... Ppfffff... Mind blowing
@allanmachado2011 Жыл бұрын
Last trick is awesome. Thank you!
@larmondoflairallen47052 жыл бұрын
That last trick is a humdinger. All those times I've duplicated queries just to get the output of an existing query up to a certain point and bogged down because it basically runs the same query twice...
@GoodlyChandeep2 жыл бұрын
Glad you found it useful!
@DuranAki-mn3ml8 ай бұрын
Hello Goodly, Dont know to send a message to you, therefor I try it from here. Is is possible to use power Query to read the background color in excel and change it to an other background color of the cell ...
@GoodlyChandeep8 ай бұрын
I don't think so.
@DuranAki-mn3ml8 ай бұрын
@@GoodlyChandeep hmm are you giving up :)
@chamoore2 жыл бұрын
the last one is mind blowing. this solves many problems. thank you mate 🙏
@excel-jodallah2 жыл бұрын
Really amazing
@GoodlyChandeep2 жыл бұрын
Thanks ✌️
@vishaljhaveri7565 Жыл бұрын
What tool you use to bring that red pencil explanation in between the videos?
@GoodlyChandeep Жыл бұрын
Zoomit
@vishaljhaveri6176 Жыл бұрын
@@GoodlyChandeep Can you please tell me some more tools which are useful & that you use on day to day basis. Really loved this one.