I really appreciate how you go through writing the M code step by step, coz its allowed me to break down the steps and know exactly where went wrong i.e. if i pulled a list instead of a table first for a function for tables. Thanks a lot!!
You lead RUclips in amount of information retained per second. All of your videos pack so much information in such a short amount of time. Thank you so much for the content.
This has came at the PERFECT time! I've been asking myself these questions with advanced transformations for nested Tables, Lists and Records. I still have many questions - but you have again shined a bright guiding light in a world full of data darkness! Thank you bhaai-jaan! PS: Also amazed that you InteliSense works 🤣 - and so rapidly! What is this wizardry!? 🧙🏽♂️
Sir, the way you explain and decode the M language is Super!, I took courses on Power Query from various other platforms but all the topics covers on the user interface and it didn't cover the topics where it requires dynamic data changes, Thanks for your conceptual training.
I don't know how many hours of work you've saved me, especially your grouped index column video. Your explanation of it was the only one I could understand clearly. : )
Great work, man! I'm just silent! I've searched this solution for weeks, but unsuccessfully when somebody didnt suggest me do this with next step. But you did it with one professional mode! :)
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.
Thanks, I had a hunch that the editor could be leveraged in this way but I just started using PowerBI and did not have the language and syntax knowledge yet. Great jumpstart -Thank you!
You are the man! I did that concat a list but it took me MANY steps and helper columns. Your way is very clear and elegant. Will be replacing mine with yours.
@@GoodlyChandeep I was able to take it one step further and added List.Sort for the items in the Concatenation. Look cleaner if the machines, in my case, are listed in order. Would not have been possible with my 'old' way of doing this. Thanks again!
You can also use the Group By and Sum the column you want to concatenate. For sure it returns an error first. But then in the formula replace List.Sum by Text.Combine and remember to complete it with the delimiter in the right place.
True, thanks for completing my answer as such avoding miscomprehension. I only wanted to point out the UI helps you discover and understand the logic behind M more then you'd realize. I often use the UI to give me a basic code that I can afterwards finetune.
This is an absolutely perfect video and solution. That is precisely what I wanted for my issue mainly. I had been bouncing my head here and there and finally found this fantastic video. Thank you for sharing it, and keep up the excellent work.
Great video. Just one suggestion that please consider making your speaking window smaller or cropping it to only show your body, so that audiences get to see more screen. I found a few videos of yours that you were deleting/moving steps that we can’t actually see what’s been deleted and moved. Nevertheless, Thanks for making such wonderful and educational videos for free to us all here.
good stuff. i usually do this SQL and python.... i find M code too much detailed and stuffed but I feel its power... hope they will continue improving the UI for non coders
This solution couldn't come in a better time, the concatenation suggestion solved the main issue I had with some data transformation. Thank you Chandeep. First video that I watched on your channel and definitely not the last one. Thank you once again.
At 4:16, the function creates a column that holds tables at each row. What confuses me is how the each function results in Tables since it is used in the context of a table. In your excellent video on the each function, the one thing I got was each would behave based on the context of what object its used in. Here its used in the context of a table and results in a table. Is it because a bunch of rows are the result and a bunch of rows = table?
Hi @Goodly What happens when there is a tie ? for example if two years have the same max value ? I am currently learning sql so in sql there are a windows functions called Row and Rank to deal with this level of complexity but I am not sure if we have any for M code.
Thanks Chandeep for sharing this. This is a very important topic you touched. I work more with Power Query than DAX and having the ability to do such amazing transformations helps a lot. Will play around with this.
Thats an amazing video! Thank you for sharing! I was actually trying to do the opposite i.e transform a field with multiple value into mutiple lines with one value only per each line, any guidance?
2 года назад
Amaizing video, thanks, with wich keys you can give format to the function in the formula bar? Like indent code or pull a row of code into the next line? thanks
Excellent. Have you got a video which would point me to the right direction for mapping two lists and filtering on condition? Say we have a one row table with col1 [a, b, c], col 2 [1, 0, 1], col3 (output) [a, c]
If you have a table just filter it on the second column containing 1. If you have lists. download this file. goodly.co.in/wp-content/uploads/2024/06/For-Rokas.xlsx
@@GoodlyChandeep appreciate your answer, but what I meant is that each row of a column contains a list. In this example, col C1 row 1 would be a list [a, b, c], col C2 row 1 would also be a list [1, 0, 1] and the output col C3 should then be a list of letters from the list in C1 corresponding to 1s from the list in C2, in this case [a, c]. I'll give it another try. Thanks for your help anyway. Your videos are indispensable. Edit: solved by creating a temp column with tables from lists in C1 and C2, then filtering that table by selecting rows that match the condition, then selecting column with letters, and turning it back to list and text combining it. Convoluted, but works. Not sure if it will scale for millions of rows though :)
Hello Sir, I have a small doubt.. What is the difference between group function and aggregate function? In real time, based on which scernio we will decide to use for group by or aggregate function ?
Hi Chandeep as usal awesome vedio. I have one doubt what if i need to get the sales for the particular year like 2004, or if need filter for one text in a column
Awesome video!! I have being looking for this future for a while, is it possible to create two list? Let's say that I want to summary the total of pallets list.count I guess and then all of the different quantity, it worked with list.distinct, I want to get a new column that summarizes the total amount of pallets per material nr and the distribution of cuantity , similar to what you did. Output should be " 2 pallets* 400 Qty, 1 pallet*250units . I can send you the information so you can see the data
You are really good. Thanks for your content. I am interested in filtering and grouping each table, and then combining. I can't seem to find a video in which you do that. Can you direct me or create something new?
Hi Chandeep, need your expertise please, I have a datasets where there are 5 different amount fields with corresponding 5 different payment date for each payment , I want to get the amount paid for each payment date by year. Any thought on how I could get it done? Thank you kindly for your expertise! Yami
Hi Chandeep.. Very useful content once again. Cheers for that. I have a use case where the source data is excel. The excel contains countries with sales figures and for every new year sales, the values are maintained in a separate column. For the reporting purpose, I have to transpose all those yearly sales columns into the rows. The query breaks if any new column for new year is added or deleted from the excel. Do you have any solution for this ?
@@YasirKhalid1 : unpivoting columns generate a M-Code which contains column header names which are unpivoted. If any of these column headers are deleted or changed in the source, the M-Code will throw an error that it couldn't find that deleted column in the source. I want to make this dynamic as possible where the unpivot code should work even if columns are added or deleted in the source.
@@kartikwat this other video below by Chandeep deals with the same topic Convert the headers to a list/table and identify pattern to generate errors either on the dimension columns or the fact columns Use that to filter the headers dynamically by using unpivot columns or unpivot other columns. Hope this helps ruclips.net/video/zoJfoMrjvi4/видео.html
@@kartikwat alternatively as a dirty hack for dynamic selection select a larger range in your query to accommodate for any future columns and use MissingField.Ignore in your formula (plz lookup syntax) to weed out columns with no data
Sir, How we can do the function similar to Indirect function in Excel, in power BI. For Ex. I am having a slicer with the name of all Indian states, If I don't select any thing from slicer, then in the table for ROW value , name of state should come and their population. If I select any state from slicer , then in the table, respective district list should appear and there population. Thanks in advance.
![Blox Fruits Dragon Rework Update [Full Stream]](http://i.ytimg.com/vi/EqDAp8udhm0/mqdefault.jpg)
I really appreciate how you go through writing the M code step by step, coz its allowed me to break down the steps and know exactly where went wrong i.e. if i pulled a list instead of a table first for a function for tables. Thanks a lot!!
Thanks :)
This guy always make me amaze with his stuff .... Thankyou chandeep... You are the best teacher ever
Thank you for your inspiring words 💚😊
You lead RUclips in amount of information retained per second. All of your videos pack so much information in such a short amount of time. Thank you so much for the content.
Thanks :)
This has came at the PERFECT time! I've been asking myself these questions with advanced transformations for nested Tables, Lists and Records. I still have many questions - but you have again shined a bright guiding light in a world full of data darkness! Thank you bhaai-jaan!
PS: Also amazed that you InteliSense works 🤣 - and so rapidly! What is this wizardry!? 🧙🏽♂️
Thanks for your nice words & also glaf it was helpful 💚
Sir, the way you explain and decode the M language is Super!, I took courses on Power Query from various other platforms but all the topics covers on the user interface and it didn't cover the topics where it requires dynamic data changes, Thanks for your conceptual training.
Thanks again, Chandeep!
Thanks a lot Carl ... Appreciate the gesture :)
I don't know how many hours of work you've saved me, especially your grouped index column video. Your explanation of it was the only one I could understand clearly. : )
Thanks!
Thank you Stefan!
I like to think of the Advanced Editor as the *true* user interface. Your videos have shown me so many new tricks! Thank you!
Glad it was useful 💚
Hi man, you explain things very clearly. You surely are one of the best teachers.
Glad you think so!
So true!
Great work, man! I'm just silent!
I've searched this solution for weeks, but unsuccessfully when somebody didnt suggest me do this with next step.
But you did it with one professional mode! :)
Cool! Glad it was helpful! 💚
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.
Thanks, I had a hunch that the editor could be leveraged in this way but I just started using PowerBI and did not have the language and syntax knowledge yet. Great jumpstart -Thank you!
🇧🇷 One of the best Power Query codes I've seen! Congratulations!
Thank you 💚
@@GoodlyChandeep 🇧🇷 Keep up the good work, man! 👊🏻👏🏻👏🏻👏🏻🔝
Simply Amazing... What a new way to get an approach to the problem....!!!
Wow, this is amazing! Thank you so much for sharing - It solved one of my main problems at work!
🙋🏼♂️from 🇩🇪
Glad it was helpful!
Very informative and knowledgeable video...you clearly explained every step. Thnx for this video.
Glad you like this
The more I watch the more I learn! Thanks again Chandeep!
Cool!
You are the man! I did that concat a list but it took me MANY steps and helper columns. Your way is very clear and elegant. Will be replacing mine with yours.
Glad it was helpful 💚
@@GoodlyChandeep I was able to take it one step further and added List.Sort for the items in the Concatenation. Look cleaner if the machines, in my case, are listed in order. Would not have been possible with my 'old' way of doing this. Thanks again!
Thanks chandeep, you are making us think in different way to solve problem in Power query...that's really helpful... excellent video..👍👍
Glad it was helpful!
You can also use the Group By and Sum the column you want to concatenate. For sure it returns an error first. But then in the formula replace List.Sum by Text.Combine and remember to complete it with the delimiter in the right place.
Not quite. You still need to create the distinct list of Customers before you can use the Text.Combine function.
True, thanks for completing my answer as such avoding miscomprehension.
I only wanted to point out the UI helps you discover and understand the logic behind M more then you'd realize. I often use the UI to give me a basic code that I can afterwards finetune.
This is an absolutely perfect video and solution. That is precisely what I wanted for my issue mainly. I had been bouncing my head here and there and finally found this fantastic video. Thank you for sharing it, and keep up the excellent work.
Glad it helped Khawar!
Great video. Just one suggestion that please consider making your speaking window smaller or cropping it to only show your body, so that audiences get to see more screen. I found a few videos of yours that you were deleting/moving steps that we can’t actually see what’s been deleted and moved. Nevertheless, Thanks for making such wonderful and educational videos for free to us all here.
Your videos are just fantastic - so clear and with content that is so relevant to business requirements. Thank you!
good stuff. i usually do this SQL and python.... i find M code too much detailed and stuffed but I feel its power... hope they will continue improving the UI for non coders
This solution couldn't come in a better time, the concatenation suggestion solved the main issue I had with some data transformation. Thank you Chandeep. First video that I watched on your channel and definitely not the last one. Thank you once again.
Excellent!
You are the best teacher of power bi😍😍
Thank you!
You are a super Genius man !!...Thanks a lot for sharing your knowledge ! love your explanation
Thank you! It is exactly what i have been searching for some days!
Glad you finally found it, Gabriella!
i learn something new from you every time
Thanks chandeep ji
Glad to hear that!
Mind blowing. Thanks for sharing such a fantastic feature.
Glad you like it!
Thanks a lot. It helps with my project. Will definitely enroll in your M course
Muchas Gracias Chandeep, me encantan tus videos, son muy claros y me han servido mucho!. Sigue así 👍👍
¡Me alegro de haber podido ayudar! :D
Wowww. Awesome. Superb explanation Chandeep!
Glad you like it !
Very very useful tutorial especially because of the value it brings where the use cases are enormous in real life job situation tasks
Thanks Alex! 😃
Awesome man. This is exactly what I was looking for! Thank you.
Fantastic!!! High level - but easy to understand!
Great to hear!
Thanks sirji🎉🎉🎉❤❤❤😊
Can you also show how to BI a Bank PDF Statement Analysis easily😮
Fantastic 💯👍
At 4:16, the function creates a column that holds tables at each row. What confuses me is how the each function results in Tables since it is used in the context of a table. In your excellent video on the each function, the one thing I got was each would behave based on the context of what object its used in. Here its used in the context of a table and results in a table. Is it because a bunch of rows are the result and a bunch of rows = table?
Hi Chandeep, great video.
Case 1, any hint on how would you concatenate values on 2 columns ?
afer using advanced group by in the Ui ...
Wonderful data manipulation Chandeep!!
Thank You Martin! 💚
This is so amazingly useful !!!! And so well explained. Thank you Chandeep !!! 😁
Glad it was useful 💚
Great work chandeep 👌👍 just want to know where is practice file of this particular video. Please tell me
Genius! Thanks for putting this training video out here. Great information!
Glad it was helpful!
Hi @Goodly What happens when there is a tie ? for example if two years have the same max value ? I am currently learning sql so in sql there are a windows functions called Row and Rank to deal with this level of complexity but I am not sure if we have any for M code.
This is again a amazing and informative Video, you are the guy from whom I learnt so much about M Language.
Thanks Kuldeep for your nice words 💚
Is this possible to use count if function in power quary.
In number of date colum ..I want use countif(range,>0.5)
Range = number of colum..
??
Your really amazing 🥰. Love from Andhra Pradesh
Thanks Chandeep for sharing this. This is a very important topic you touched. I work more with Power Query than DAX and having the ability to do such amazing transformations helps a lot. Will play around with this.
Thanks for your nice words Jay! 💚
@@GoodlyChandeep And utilized it (Group By - concatenate values of a List) today on a real project as well. Thanks again!
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
Thats an amazing video! Thank you for sharing! I was actually trying to do the opposite i.e transform a field with multiple value into mutiple lines with one value only per each line, any guidance?
Amaizing video, thanks, with wich keys you can give format to the function in the formula bar? Like indent code or pull a row of code into the next line? thanks
You are teaching for video all very well😍.thank !
Glad you like it 💚
Excellent. Have you got a video which would point me to the right direction for mapping two lists and filtering on condition? Say we have a one row table with col1 [a, b, c], col 2 [1, 0, 1], col3 (output) [a, c]
If you have a table just filter it on the second column containing 1.
If you have lists. download this file. goodly.co.in/wp-content/uploads/2024/06/For-Rokas.xlsx
@@GoodlyChandeep appreciate your answer, but what I meant is that each row of a column contains a list. In this example, col C1 row 1 would be a list [a, b, c], col C2 row 1 would also be a list [1, 0, 1] and the output col C3 should then be a list of letters from the list in C1 corresponding to 1s from the list in C2, in this case [a, c]. I'll give it another try. Thanks for your help anyway. Your videos are indispensable.
Edit: solved by creating a temp column with tables from lists in C1 and C2, then filtering that table by selecting rows that match the condition, then selecting column with letters, and turning it back to list and text combining it. Convoluted, but works. Not sure if it will scale for millions of rows though :)
When you create video in beginning the quality was not good but now the quality is good
hi Kunal, why did not like the videos before. can you point out what do you like now and why?
This was amazing! Love how you explain everything!
Thank you!
Thank you ! This was another great educational video.
Brilliant! Thanks a lot for this and many other short practical videos you produced and shared! :)
Glad you enjoyed it!
Thank you for sharing your advance tricks in PQ
Glad you like it 💚
Really great video... But what would you do if there were more than 1 year with the max value??
Not sure, but probably some ranking technique to extract every record ranked 1.
thanks for your video. let me further understand the lookup in power query
Seriously, genius. 🎉
Hello Sir, I have a small doubt.. What is the difference between group function and aggregate function? In real time, based on which scernio we will decide to use for group by or aggregate function ?
Dude much appreciated! Great explanation and came at a great time as well
You are always awesome to come up with new learning for us. It is very helpful.
Glad it was helpful 💚
Amazing!!!!!!! thank you for sharing your knowledge!!
Glad you enjoyed it Eliseo!
Hi Chandeep as usal awesome vedio.
I have one doubt what if i need to get the sales for the particular year like 2004, or if need filter for one text in a column
Awesome video!! I have being looking for this future for a while, is it possible to create two list? Let's say that I want to summary the total of pallets list.count I guess and then all of the different quantity, it worked with list.distinct, I want to get a new column that summarizes the total amount of pallets per material nr and the distribution of cuantity , similar to what you did. Output should be " 2 pallets* 400 Qty, 1 pallet*250units . I can send you the information so you can see the data
Thank you so much. This is exactly what I need.
Glad it helped
Glad to find your content. Subscribed.
Glad you have subscribed! 💚
Thanks Chandeep! Amazing scenarios and their solutions
I love how you breakdown approaching M coding and tweaking the code generated by powerquery
Glad you love it 💚
great video, Chandeep. just wish you supplied your dataset to make learning easier.
All the blogs are supplied with dataset - goodly.co.in/blog/
@@GoodlyChandeep fantastic. thank you!
Hello Chandeep, nice work. I have a question. Is it possible to invoke a function without creating a new column?
Yes, it is possible!
Thanks!! I have seen similar other solution and this is the most simple and efficient. It is like 3 step only 😎
Glad you like it!
Thank you for your really usefull tips.
When extracting the year, how do you handle the error if you have more than one maximum sales value per person?
Table.Max function only picks up 1 value even if there are multiple values found.
Thanks for the wonderful video! Very precise and useful!
Glad you like it 💚
You are really good. Thanks for your content. I am interested in filtering and grouping each table, and then combining. I can't seem to find a video in which you do that. Can you direct me or create something new?
thank you so much, thank youtube suggest what I am looking for
Actually, thank you but we can do that with 1 of M- Code function (table.column), and then extract our valu with any type of delimiter you need ?
Hi Chandeep,
When you are taking a live power bi training session?
Thanks for the video, very informative 👍👍
Glad you like it 💚
Excellent tutorial. You teach how to think in M Language. Thank you very much. :-)
Glad you like it 💚
I just wonder how to show both MaxSalesYears in case there were 2 or 3 years with the same max Sales Value.
I was a little confused as well because I thought we were going to SUM the sales by Year first or Sum them by customer first.
Good stuff Chandeep
Great. Thanks so much. Loved it!
Glad you love it! 💚
You are really the best!!
Thank you 💚
Muito boa a dica, obrigado por compartilhar!
Hi Chandeep, need your expertise please, I have a datasets where there are 5 different amount fields with corresponding 5 different payment date for each payment , I want to get the amount paid for each payment date by year. Any thought on how I could get it done? Thank you kindly for your expertise! Yami
Bro... you made my day.
and if not Text insted is a value?
Sir, lesson was great help.. pls help me on how to get sum the of concatenated values in same query.
Monica, you can use the Group By feature and SUM happens to be a standard column calculation that you can use!
Such a great content 👏
Thank you 💚
Hi Chandeep.. Very useful content once again. Cheers for that.
I have a use case where the source data is excel. The excel contains countries with sales figures and for every new year sales, the values are maintained in a separate column.
For the reporting purpose, I have to transpose all those yearly sales columns into the rows.
The query breaks if any new column for new year is added or deleted from the excel.
Do you have any solution for this ?
Use unpivot to bring years in one column and sales in another column
@@YasirKhalid1 : unpivoting columns generate a M-Code which contains column header names which are unpivoted.
If any of these column headers are deleted or changed in the source, the M-Code will throw an error that it couldn't find that deleted column in the source.
I want to make this dynamic as possible where the unpivot code should work even if columns are added or deleted in the source.
@@kartikwat this other video below by Chandeep deals with the same topic
Convert the headers to a list/table and identify pattern to generate errors either on the dimension columns or the fact columns
Use that to filter the headers dynamically by using unpivot columns or unpivot other columns. Hope this helps
ruclips.net/video/zoJfoMrjvi4/видео.html
@@kartikwat alternatively as a dirty hack for dynamic selection select a larger range in your query to accommodate for any future columns and use MissingField.Ignore in your formula (plz lookup syntax) to weed out columns with no data
You are awesome! thank you very much!
how to enable auto suggestion formula when we edit formula bar?
Thanks a lot again bro, great tips
Always welcome!
Very good, well done.
Thank you 💚
Great video. Is it possible to do full course on power query?
Already have one! Check here goodly.podia.com/power-query-course
@@GoodlyChandeep I have noticed it is 1 year old. Are you planning to upgrade it to capture all your tricks and anything new?
Sir, How we can do the function similar to Indirect function in Excel, in power BI. For Ex. I am having a slicer with the name of all Indian states, If I don't select any thing from slicer, then in the table for ROW value , name of state should come and their population. If I select any state from slicer , then in the table, respective district list should appear and there population.
Thanks in advance.