You lead TH-cam 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.
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.
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!
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.
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.
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! :)
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, 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.
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.
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 ?
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
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.
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
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.
Can I make group by bin in power query, I mean if I have a table with some items and each one has its quantity and price and there is a running total column ,can I create group of items (1,2,3...)where each group doesn't have running total more than of 1000$?
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
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?
Sir, plz make video for below. If 1 table has 9 column, 2 table has 10 and 3 table has 8 . Then how can we make equal all column for all table.. like we want to insert dummy column for missing column in tables and make equal like 10 column for each table.
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, I need yours assistance for one thing - I have two different table - Table1 & Table2 Both tables has records with n number of columns, one column is common in both table which is Country. table2 has another field called Status=Active, Inactive. so I want a one output Matrix Table (similarly mentioned below), where I want to get the Count of both tables based on Country Condition: Table1: no condition (count) Table2 : based on Status = Active (countif) Country Count from Table1 Count from Table2 ------------------------------------------------------------------------------------------------------------------- India 23 39 Canada 44 48 Brazil 51 7 United States 17 73 how we can get it in Power Bi, i have made but second table count coming sum of table2 in each row.. please help
This is basically what I have been trying to do. Except, I want to create visualization so when you click on a title/movie, all the cast members pop up. Is that something you can explain?
I don't understand, isn't that a simple filter on the movie column that shows all the cast members? I am not sure where and why are you getting stuck :(
Hi, I have below data with me. Column-A Abhay Abhay Abhay Ravikant Ravikant Ravikant Column-B January-2024 February-2024 March-2024 January-2024 February-2024 March-2024 Column-C (Sales) 1000 2000 3000 1200 1400 1600 I want 1) In Column A the row A1 & A2 should only Abhay & Ravikant. 2) Column B Should be January-2024, Column C as February-2024 & Column D as March-2024. 3) Column E should have Sales numbers each month. Please help
I have two columns in power query. One is a worker name and the other a column formatted as time where he records his time on different stages of a project. I need to group per worker name and then get total duration for his project. Any ideas? Tried everything.
It looks like This: Name Time Name1 14:00:00 Name1 14:10:00 Name1 14:15:00 Name2 8:00:00 Name2 8:10:00 Name2 8:15:00 Name2 8:20:00 I then need to group by the name and get the duration for each name
This is a very advanced question, I think nobody asked before. If I have like 50 columns, and I want to group by only 2 of them, how can I agreggate automatically the other 48? 😈
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 th-cam.com/video/zoJfoMrjvi4/w-d-xo.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
Hi Chandeep, what shall I say. It is boring to leave always the same comment 🤣. But I do have to questions concerning this. I have open item lists at various dates. I want to see which invoice is when in the OP list (OP report date) and what the invoice value is (Step invoice). I get the desired result, but would like to apply your solution in one step. Thanks for your advice. GrpRows_OPReportDate = Table.Group(ChgType_OPReportDate, {"Invoice No (MBP/GSP)"}, { {"OP Report Date", each Text.Combine(_[OP_Date],", "), type nullable text },
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 TH-cam 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 :)
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.
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 💚
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!
Hi man, you explain things very clearly. You surely are one of the best teachers.
Glad you think so!
So true!
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..
??
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.
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!
Wow, this is amazing! Thank you so much for sharing - It solved one of my main problems at work!
🙋🏼♂️from 🇩🇪
Glad it was helpful!
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! 💚
Thanks sirji🎉🎉🎉❤❤❤😊
Can you also show how to BI a Bank PDF Statement Analysis easily😮
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 ...
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.
Your videos are just fantastic - so clear and with content that is so relevant to business requirements. Thank you!
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.
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?
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! 😃
The more I watch the more I learn! Thanks again Chandeep!
Cool!
Muchas Gracias Chandeep, me encantan tus videos, son muy claros y me han servido mucho!. Sigue así 👍👍
¡Me alegro de haber podido ayudar! :D
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.
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 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!
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.
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 ?
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
Hi Chandeep,
When you are taking a live power bi training session?
Awesome man. This is exactly what I was looking for! Thank you.
Great work chandeep 👌👍 just want to know where is practice file of this particular video. Please tell me
Very informative and knowledgeable video...you clearly explained every step. Thnx for this video.
Glad you like this
i learn something new from you every time
Thanks chandeep ji
Glad to hear that!
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!
Simply Amazing... What a new way to get an approach to the problem....!!!
Thank you! It is exactly what i have been searching for some days!
Glad you finally found it, Gabriella!
Very informative. Can we get the source file ?
Fantastic 💯👍
Thank you ! This was another great educational video.
Genius! Thanks for putting this training video out here. Great information!
Glad it was helpful!
This was amazing! Love how you explain everything!
Thank you!
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
thanks for your video. let me further understand the lookup in power query
Mind blowing. Thanks for sharing such a fantastic feature.
Glad you like it!
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.
You are always awesome to come up with new learning for us. It is very helpful.
Glad it was helpful 💚
Can I make group by bin in power query, I mean if I have a table with some items and each one has its quantity and price and there is a running total column ,can I create group of items (1,2,3...)where each group doesn't have running total more than of 1000$?
Fantastic!!! High level - but easy to understand!
Great to hear!
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!
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
Your really amazing 🥰. Love from Andhra Pradesh
Thank you for sharing your advance tricks in PQ
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?
sir is it possible to get row data for practice purpose
and if not Text insted is a value?
Sir, plz make video for below.
If
1 table has 9 column,
2 table has 10
and 3 table has 8 .
Then how can we make equal all column for all table.. like we want to insert dummy column for missing column in tables and make equal like 10 column for each table.
Use Table.Combine function. Watch this video
th-cam.com/video/qCHvpbV-Jkg/w-d-xo.html
0:37 to 6:00 minutes mark
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 :)
This is so amazingly useful !!!! And so well explained. Thank you Chandeep !!! 😁
Glad it was useful 💚
Thank you so much. This is exactly what I need.
Glad it helped
Wowww. Awesome. Superb explanation Chandeep!
Glad you like it !
Can we perform the same task using mouse and keyboard
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!
Wonderful data manipulation Chandeep!!
Thank You Martin! 💚
Good stuff Chandeep
Seriously, genius. 🎉
how to create the column "others" dynamic ???
Thanks for the wonderful video! Very precise and useful!
Glad you like it 💚
Thank you for your really usefull tips.
Perfect! Thanks so much!
Welcome William!
You are teaching for video all very well😍.thank !
Glad you like it 💚
Hello Chandeep, nice work. I have a question. Is it possible to invoke a function without creating a new column?
Yes, it is possible!
Hello,
I need yours assistance for one thing -
I have two different table - Table1 & Table2
Both tables has records with n number of columns, one column is common in both table which is Country. table2 has another field called Status=Active, Inactive.
so I want a one output Matrix Table (similarly mentioned below), where I want to get the Count of both tables based on Country
Condition:
Table1: no condition (count)
Table2 : based on Status = Active (countif)
Country Count from Table1 Count from Table2
-------------------------------------------------------------------------------------------------------------------
India 23 39
Canada 44 48
Brazil 51 7
United States 17 73
how we can get it in Power Bi, i have made but second table count coming sum of table2 in each row.. please help
This is basically what I have been trying to do. Except, I want to create visualization so when you click on a title/movie, all the cast members pop up. Is that something you can explain?
I don't understand, isn't that a simple filter on the movie column that shows all the cast members? I am not sure where and why are you getting stuck :(
Hi, I have below data with me.
Column-A
Abhay
Abhay
Abhay
Ravikant
Ravikant
Ravikant
Column-B
January-2024
February-2024
March-2024
January-2024
February-2024
March-2024
Column-C (Sales)
1000
2000
3000
1200
1400
1600
I want 1) In Column A the row A1 & A2 should only Abhay & Ravikant. 2) Column B Should be January-2024, Column C as February-2024
& Column D as March-2024. 3) Column E should have Sales numbers each month.
Please help
Excellent tutorial. You teach how to think in M Language. Thank you very much. :-)
Glad you like it 💚
Finished watching
Amazing!!!!!!! thank you for sharing your knowledge!!
Glad you enjoyed it Eliseo!
Great. Thanks so much. Loved it!
Glad you love it! 💚
You are awesome! thank you very much!
Nicely explained sir
Thank you 💚
Thank you, Chandeep. I finally understood '_'.
Glad it was helpful to you 💚
I have two columns in power query. One is a worker name and the other a column formatted as time where he records his time on different stages of a project. I need to group per worker name and then get total duration for his project. Any ideas? Tried everything.
It looks like This:
Name Time
Name1 14:00:00
Name1 14:10:00
Name1 14:15:00
Name2 8:00:00
Name2 8:10:00
Name2 8:15:00
Name2 8:20:00
I then need to group by the name and get the duration for each name
It's straight SUM operation in Group By
The very first thing i uttered after watching this video: Banda khatarnaak h bhut!!
Haha..! Thank you 😄💚
Thanks a lot again bro, great tips
Always welcome!
Great video!
Thank you!
Thanks for the video, very informative 👍👍
Glad you like it 💚
what if I want to extract 2 columns from the Record? That's exacly what I really need 🙏
If you want extract 2 columns, instead [Year] write: [[Year],[Sales]]
@@PawelZ-w2p thank you👍
You are really the best!!
Thank you 💚
Amazing👏
Plz share dataset to practice sir
This is a very advanced question, I think nobody asked before.
If I have like 50 columns, and I want to group by only 2 of them, how can I agreggate automatically the other 48? 😈
This was awesome
Thank you 💚
Very helpful
thak you
You are awesome.....
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
th-cam.com/video/zoJfoMrjvi4/w-d-xo.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
Very good, well done.
Thank you 💚
Nice
Hi Chandeep, what shall I say. It is boring to leave always the same comment 🤣. But I do have to questions concerning this. I have open item lists at various dates. I want to see which invoice is when in the OP list (OP report date) and what the invoice value is (Step invoice). I get the desired result, but would like to apply your solution in one step. Thanks for your advice.
GrpRows_OPReportDate = Table.Group(ChgType_OPReportDate, {"Invoice No (MBP/GSP)"},
{
{"OP Report Date", each Text.Combine(_[OP_Date],", "), type nullable text },
Such a great content 👏
Thank you 💚
Genius 🙏
Thank you!
Good content
Thank you 💚