Yes, Sir!!!! I have been on family emergency for last 6 months. I was right in the middle of writing a new book about M Code in Power Query, but it got put on hold. Soon, though : )
Another epic video, Mike. Thanks for posting. Groupby has so many uses as both a step in it's own right and as an intermediate step that it makes life much easier than in the good (?) old days of pages long worksheet formulae. With a little extra preparation you can get them really robust; no more hours of rewriting a whole worksheet because the management team sneezed out a bright idea to rename/add/delete a column to make the report prettier. I am with you on the addition of null lnes into the dataset, I cannot imagine why anyone would want to do such a thing! There should be a special circle of hell for them and the guys that isnsist on centred/merged cells all over the workbook.
You are cracking me up. You say wise and funny things: "because the management team sneezed out a bright idea "... All the time, with snot everywhere : ( "special circle of hell for them" and centered/merged everywhere... Thanks for the Teammateshipness ; )
Thank you for the great video Mike. I really enjoy when I get to use the group by function so I this was especially fun. Thank you so much Mike! Right Click+G is a something I learned especially helpfull.
Hey Mike, On the blank rows: Mr. Excel did a video on that not so long ago. We responded to that: someone came up with the & trick and I came up with the empty-row table. So, you don’t need any column name, and you don’t need the null: it’s just a small bunch of curly brackets you need to put in.
@@GeertDelmulle Yah, I saw that Mr Excel video. I was surprised he used the longer M Code form the book (he's a book seller, though : ) ). Maybe he did not see the comment from Bill Szysz... but I thought he showed it in video. I thought the Bill Szysz solution seemed the easiest.
@@excelisfun Yep! I still used Table.Combine but someone else came up with the &. And, of course, the main thing here: an empty table, is -er- well, exactly that… :-) But all jokes aside: it’s also most robust, we can add empty rows (as many as we need) to any table with any column names. And of course, the next step would be to make it into a custom M-function, because we couldn’t be bothered with the same details over and over… You know: we’re lazy like that. :-) You should see the faces of the people when they see the seemingly impossible transformations we can do with just one custom function call. From desperation to disbelief… And it’s repeatable, too. Go figure. Works wonders for one’s reputation… lol :-)
@@excelisfun I am sure you also do lots of learning in addition to designing and presenting the videos. Thank you for your time that you invest into sharing your knowledge.
Hi, this tick with text.combine is really great. Is there a possibility to combine also numbers (with out converting to text)? Currently I need two steps, one step in group_by: {"Single_Qty", each [Qty]}, and a 2nd step to extract the values: Extract Values from List = Table.TransformColumns(GroupedRows, {"Single_Qty", each Text.Combine(List.Transform(_, Text.From), "#(lf)"), type text}) Do you have any approach to combine it in one group_by step? Thank and Cheers.
Great lesson as usual! I notice that in the Homework queries you use "type nullable number". I've been using just "type number", and don't remember ever seeing the UI generate "type nullable number". Is there any difference in the two?
Check out what I just wrote in previous comment: Data Type nullable number just means that a null and number are allowed in the column by Data Type (two data types in one column is not usually allowed - although null is technically "the absence of data"). nullable number literally classify a value null in addition to a number type. Data Type number just classifies the one data type number. But you can have a null in a number column because null just means the absence of data. But here is the crazy thing, when I build two identical fields, each with name numbers and one null, then add Data Type type number and the other gets Data Type type nullable number: 1) SUM, COUNTROWS and DISTINCTCOUNT DAX function all get same answer. 2) SUM, COUNT, COUNT(UNIQUE()) Excel Worksheet functions all get same answer. 3) A merge between the Data Type type number Field and the Data Type type nullable number Field, the merge/join works. 4) When you try the M Code function Value.Type on both columns, you get the same result I could not find much practical difference. 5) The only thing I have seen online is from Chris Webb and he shows that if you connect to an SQL Database and query performing is performed, then the SQL code written is slightly different to incorporate the nulls, but the performance difference between the two would always need to be tested... I guess there is some non-equivalency issue somewhere, but I could not find it except for the SQL code written during query folding. Maybe @Bill Szysz or someone else knows? However, the user interface does some time pick between the two and will deliver a Data Type type number sometimes and a Data Type type nullable number sometimes. I can't remember an example right off top of my head, but I have definitely seem it. i will keep my eye out for it now.
For eg from column I have 123 and in To column I have 130 now need to get new column with between range like 124,125,126,127, till the end range in dax
No, I have a DAX one and a few more. My Mom got sick and I have been taking care of her so it has been put on hold. The DAX one should be out in a few weeks...
Mr girvin To work as a freelancer Excel on freelance sites Should I learn macros and VBA or I can work on freelance sites without studying macros and VBA ???
You can't ask me because I never learned VBA at a high level. So I do not have the correct perspective. But I do know that Power Query and the new formula calculation engine and all that goes with that allows one to replace older VBA code. However, I guess i could say that you should learn it because the human resource departs still ask for VLOOKUP and Macros, even though we don't use them much any more.
The home page of my excelisfun channel here at RUclips has classes and an intro video about it all. Here is the class you are looking for: ruclips.net/p/PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
I don't just have formula videos. All my classes are full concepts, theories, functions and formulas. My MSPTDA class is here (probably not what you want because it is so comprehensive): ruclips.net/p/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1 Videos #3-12 are all about Power Query. Videos #13 - onward are all about DAX
Data Type nullable number just means that a null and number are allowed in the column by Data Type (two data types in one column is not usually allowed - although null is technically "the absence of data"). nullable number literally classify a value null in addition to a number type. Data Type number just classifies the one data type number. But you can have a null in a number column because null just means the absence of data. But here is the crazy thing, when I build two identical fields, each with name numbers and one null, then add Data Type type number and the other gets Data Type type nullable number: 1) SUM, COUNTROWS and DISTINCTCOUNT DAX function all get same answer. 2) SUM, COUNT, COUNT(UNIQUE()) Excel Worksheet functions all get same answer. 3) A merge between the Data Type type number Field and the Data Type type nullable number Field, the merge/join works. 4) When you try the M Code function Value.Type on both columns, you get the same result I could not find much practical difference. 5) The only thing I have seen online is from Chris Webb and he shows that if you connect to an SQL Database and query performing is performed, then the SQL code written is slightly different to incorporate the nulls, but the performance difference between the two would always need to be tested... I guess there is some non-equivalency issue somewhere, but I could not find it except for the SQL code written during query folding. Maybe @Bill Szysz or someone else knows?
@@excelisfun Thank you for researching it Mike. Thats why I asked,cos I played around with it and didnt see any differnce between types or values. So either I did sth wrong or there was no difference, thats why I asked. Thank you so much!!!
@@ExcelInstructor There is a difference. Internally number is number and nullable number is number OR null. But does the difference do anything - so far it is only in query folding.
When my colleagues ask for training resources to learn PQ, your channel is the first place I send them to. DON'T STOP DOING WHAT YOU'RE DOING!
Yes, Sir!!!! I have been on family emergency for last 6 months. I was right in the middle of writing a new book about M Code in Power Query, but it got put on hold. Soon, though : )
Another epic video, Mike. Thanks for posting. Groupby has so many uses as both a step in it's own right and as an intermediate step that it makes life much easier than in the good (?) old days of pages long worksheet formulae. With a little extra preparation you can get them really robust; no more hours of rewriting a whole worksheet because the management team sneezed out a bright idea to rename/add/delete a column to make the report prettier. I am with you on the addition of null lnes into the dataset, I cannot imagine why anyone would want to do such a thing! There should be a special circle of hell for them and the guys that isnsist on centred/merged cells all over the workbook.
You are cracking me up. You say wise and funny things: "because the management team sneezed out a bright idea "... All the time, with snot everywhere : ( "special circle of hell for them" and centered/merged everywhere... Thanks for the Teammateshipness ; )
@@excelisfun Thanks, Mike. It is funny now but when in the situation of yet another hours long update it could be dire lol. Proud to be a teammate!
Wow, greatly widen my horizon on how to use power query and group by to do data transformations.
Yes!!! I Love to hear that. That is the goal : )
Thank you for the great video Mike. I really enjoy when I get to use the group by function so I this was especially fun. Thank you so much Mike! Right Click+G is a something I learned especially helpfull.
Yep, Right-click Key + G Rules!!!
Another epic videos from ExcelIsfun! For M Code is difficult to find such quality content, thanks MIke!
Glad this M Code fun is good for you and our Team!!!! : )
Amazing quality as usual. The last groupkind is crazy.
Great video! The only pity is that there was no example with the 5th argument of the Table.Group() function.
Glad you like it!!!!
Your videos are like treats for excel lovers❤
Yes!!!!! Excel Over Treats!!!!!
M Code is a powerful tool to transform data. Thank you Mike for making this video. Waiting for the next video about DAX 🙂
Glad you like it, My Ghostly Friend!!!!
As others have said, epic! Great job as always Mike!
Thanks for the continued support, Chris M!!!!!
Excellent as always.. Thank you 🙏
You are welcome, Marben!!!
Thanks Mike. Great Video
Amazing tutorial like always...Thanks for you knowledge!
You are welcome, long tine viewer Joajuim!!!
So great Mike, I like the new addrankcolumn, it feels handy will definitely test it ;)🎉
It was new to me. It is a great addition to M!!!!!
Great topic with great stuff in an epic course! What a treat. :-)
&#table({},{{}}), right? lol
Hey Mike,
On the blank rows: Mr. Excel did a video on that not so long ago.
We responded to that: someone came up with the & trick and I came up with the empty-row table.
So, you don’t need any column name, and you don’t need the null: it’s just a small bunch of curly brackets you need to put in.
@@GeertDelmulle Yah, I saw that Mr Excel video. I was surprised he used the longer M Code form the book (he's a book seller, though : ) ). Maybe he did not see the comment from Bill Szysz... but I thought he showed it in video. I thought the Bill Szysz solution seemed the easiest.
@@excelisfun Yep! I still used Table.Combine but someone else came up with the &.
And, of course, the main thing here: an empty table, is -er- well, exactly that… :-)
But all jokes aside: it’s also most robust, we can add empty rows (as many as we need) to any table with any column names.
And of course, the next step would be to make it into a custom M-function, because we couldn’t be bothered with the same details over and over… You know: we’re lazy like that. :-)
You should see the faces of the people when they see the seemingly impossible transformations we can do with just one custom function call. From desperation to disbelief… And it’s repeatable, too. Go figure.
Works wonders for one’s reputation… lol :-)
@@GeertDelmulle that beautiful ceaseless creativity and masterful logic in the first part, then the lazy... lol
Amazing.
And such power ; )
Thank you for your great work!
Btw, nice Warriors cap😄😄😄
Yah, I grew up in Oakland, CA where my Oakland Warriors are king : ) : ) : ) Glad you like the video too!
Thanks Mike for this EXCELlent video.
You are welcome, Most Awesome Fellow Teacher : ) : ) : )
A great topic. Totally new for me. All of it. Thank you!
Yes!!!! All new: means tons of knowledge and expanded brain and more fun with analysis.
@@excelisfun I am sure you also do lots of learning in addition to designing and presenting the videos. Thank you for your time that you invest into sharing your knowledge.
@@zt.5677 I do. Many of the longer video take 100 - 200 hours, much of it research nd trial and error. It's fun! You are welcome for it all : )
super Video :)
Thank you Mike for your hard work
You are welcome for the hard work!
Request you make a video it will be great help sir for us and learner’s thanks in advance
je cherchais la manip pour la ligne vide. Merci!!!! Merci!!!!!!! Merci!!!!!
Been looking forward to this!
It should be great fun : ) : )
Hi, this tick with text.combine is really great. Is there a possibility to combine also numbers (with out converting to text)?
Currently I need two steps, one step in group_by: {"Single_Qty", each [Qty]}, and a 2nd step to extract the values: Extract Values from List = Table.TransformColumns(GroupedRows, {"Single_Qty", each Text.Combine(List.Transform(_, Text.From), "#(lf)"), type text})
Do you have any approach to combine it in one group_by step?
Thank and Cheers.
Great lesson as usual! I notice that in the Homework queries you use "type nullable number". I've been using just "type number", and don't remember ever seeing the UI generate "type nullable number". Is there any difference in the two?
Check out what I just wrote in previous comment:
Data Type nullable number just means that a null and number are allowed in the column by Data Type (two data types in one column is not usually allowed - although null is technically "the absence of data"). nullable number literally classify a value null in addition to a number type.
Data Type number just classifies the one data type number. But you can have a null in a number column because null just means the absence of data.
But here is the crazy thing, when I build two identical fields, each with name numbers and one null, then add Data Type type number and the other gets Data Type type nullable number:
1) SUM, COUNTROWS and DISTINCTCOUNT DAX function all get same answer.
2) SUM, COUNT, COUNT(UNIQUE()) Excel Worksheet functions all get same answer.
3) A merge between the Data Type type number Field and the Data Type type nullable number Field, the merge/join works.
4) When you try the M Code function Value.Type on both columns, you get the same result
I could not find much practical difference.
5) The only thing I have seen online is from Chris Webb and he shows that if you connect to an SQL Database and query performing is performed, then the SQL code written is slightly different to incorporate the nulls, but the performance difference between the two would always need to be tested...
I guess there is some non-equivalency issue somewhere, but I could not find it except for the SQL code written during query folding.
Maybe @Bill Szysz or someone else knows?
However, the user interface does some time pick between the two and will deliver a Data Type type number sometimes and a Data Type type nullable number sometimes. I can't remember an example right off top of my head, but I have definitely seem it. i will keep my eye out for it now.
Hi sir I hope you doing well
Sir there is ask from my client on the range numbers
For example I have from and to column in table now need to get range in between
For eg from column I have 123 and in To column I have 130 now need to get new column with between range like 124,125,126,127, till the end range in dax
I have no idea how to do what you have asked. You can try this great Excel / Power BI question site; mrexcel.com/board
Wahou!
Hi Mike, is this the last one of your formidable series?
No, I have a DAX one and a few more. My Mom got sick and I have been taking care of her so it has been put on hold. The DAX one should be out in a few weeks...
@@excelisfun Thank you very much for taking th etime to answer. Hope the best to your mom.
well informative
Glad you like it, Mahesh!!!
Mr girvin
To work as a freelancer Excel on freelance sites
Should I learn macros and VBA or I can work on freelance sites without studying macros and VBA ???
You can't ask me because I never learned VBA at a high level. So I do not have the correct perspective. But I do know that Power Query and the new formula calculation engine and all that goes with that allows one to replace older VBA code.
However, I guess i could say that you should learn it because the human resource departs still ask for VLOOKUP and Macros, even though we don't use them much any more.
Is this playlist incomplete because I don't see the videos are in sequence and in between some numbers are missing. Kindly clear it.
Thanks
The home page of my excelisfun channel here at RUclips has classes and an intro video about it all. Here is the class you are looking for:
ruclips.net/p/PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
Where can I learn complete power query and DAX formulas. Please suggest..
I don't just have formula videos. All my classes are full concepts, theories, functions and formulas. My MSPTDA class is here (probably not what you want because it is so comprehensive): ruclips.net/p/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1
Videos #3-12 are all about Power Query. Videos #13 - onward are all about DAX
I've tried to make a few videos about basic M code formulas, but they won't be as good and polished as what @ExcelIsFun does.
Mike, what is the difference between Type number and Type nullable number ?
I cannot find the information about the difference
Data Type nullable number just means that a null and number are allowed in the column by Data Type (two data types in one column is not usually allowed - although null is technically "the absence of data"). nullable number literally classify a value null in addition to a number type.
Data Type number just classifies the one data type number. But you can have a null in a number column because null just means the absence of data.
But here is the crazy thing, when I build two identical fields, each with name numbers and one null, then add Data Type type number and the other gets Data Type type nullable number:
1) SUM, COUNTROWS and DISTINCTCOUNT DAX function all get same answer.
2) SUM, COUNT, COUNT(UNIQUE()) Excel Worksheet functions all get same answer.
3) A merge between the Data Type type number Field and the Data Type type nullable number Field, the merge/join works.
4) When you try the M Code function Value.Type on both columns, you get the same result
I could not find much practical difference.
5) The only thing I have seen online is from Chris Webb and he shows that if you connect to an SQL Database and query performing is performed, then the SQL code written is slightly different to incorporate the nulls, but the performance difference between the two would always need to be tested...
I guess there is some non-equivalency issue somewhere, but I could not find it except for the SQL code written during query folding.
Maybe @Bill Szysz or someone else knows?
@@excelisfun Thank you for researching it Mike.
Thats why I asked,cos I played around with it and didnt see any differnce between types or values.
So either I did sth wrong or there was no difference, thats why I asked.
Thank you so much!!!
@@ExcelInstructor There is a difference. Internally number is number and nullable number is number OR null. But does the difference do anything - so far it is only in query folding.
Hey mike! Is there any video on latitude and longitude calculation?
I have a way to do it in Power BI desktop with a map visual in this video: ruclips.net/video/TjSnQ4VDHTE/видео.html
First lovely comment sir
You get the lovely First Place Trophy!!!! : )
Sir I need one small help from you on power bi side