I have been watching you videos since I accidentally found your channel and became you fan instantly. This is another one video that I really appreciate. Thank you very much.
There is lot easier solution to this as below. Still appreciate your work. 1. Just creat 2 index columns one starting with 0 and other with 1. 2. Do a self left join selecting category col and 1st index col" to "category col and 2nd index col" 3. Expand value Boom done🎉
Rather than applying a merge.. I'd prefer the Power Query solution here - ruclips.net/video/a7O2UlbTAvA/видео.html Merges can be expensive on large tables.
Many thanks for this helpful video! I have been able to adapt its methods to creating a series of lagged values for thousands of products, in order to perform time series analyses. This method is very, very much faster to execute than my previous crude method of Merging the same table to itself over and over. (And by the way, ALL of your videos are similarly enlightening.)
Awesome Chandeep! Love the way you transform with M vs. many multiple steps through the UI. A great learning experience! Thanks for sharing your solution and for hosting the challenge :)) Thumbs up!!
Thanks for the video. It would be good if you could start with showing how to refer to previous row value in a single table, and then move to show how to refer to previous row value in nested tables. I think it would make it easier for the audiences to understand and master the technique step by step. Nevertheless, I have learnt a lot watching your videos 😀
"This video proves to be quite useful. Could you kindly consider creating another video that demonstrates the process of performing Power Query steps for calculating a running total at the category level?"
Thanks for the Video - It is very helpful - what if I need to get a specific Order or Index within the group based on other fields - For example what if I want to rollup to another field called step that isn't in consecutive order for example step 2, 3a, 3b, 4, 5a, 5b, 6a, 6b, 6c, 6d, 7. I would need the index to be dynamic because there would be another field that I would filter by that would mean not all steps would be included in the result - so that when filtered it might include 2, 3a, 4, 5b, 6c, 6d and 7. but would still need to be ordered within the grouping category and the previous value would only come from the steps provided. the amount field would need to rollup. Hopefully this makes sense.
Great Video Chandeep, got to this video when i was stuck on a problem to group and apply some calculation. this helped in a great extent. One more question for me to succeed. Once i capture the sub table and use table.addcolumn to create new column as you explained, is it possible to apply If statements to it. For ex. if the captured table has multiple columns and i want to compare the value of 2 of the columns which decides the value of new column. Thanks in advance for any insights
hi Chandeep. Excellent tutorial. Thanks. Just a question about the intermdiatery step of creating 'AllDataTable'. Can you created an added PrevValue coumn direct to the FullTable rather than using the AllDataTable' step?? Is there difficulty in writing the M formula? Curious to know why you did not create the previous column direct in addition the existing table. Thanks in advance
@@GoodlyChandeep i have a question from this video, though. I was trying to eliminate the following step of creating a new column subtracting Previous Value from Current Value. In the Create Custom Column step I was trying to return each (AllDataTable[Value] - AllDataTable[Value]{[Index] - 1 } and it was throwing an error for all rows. Why is this? Here's the code I used in the Added Custom Column interface. let AllDataTable = [all_data], PreRowValue = Table.AddColumn([all_data], "PrevValue", each try (AllDataTable[Value] - AllDataTable[Value]{[Index] - 1}) otherwise null) in PreRowValue
Hi Very good!!!. But if is not the previous row. Is a specific row based on a criteria of itens \ wharehouse and for some columns instead the complete row
Hi Chandeep This is so nice Thanks alot Let me ask you, is it possible to make a video on Refer Previous Row for Subgroup using DAX for performance aspects Best regards,
Thank you for the video… I have a scenario . Our employees register in a website and sometimes they enter wrong PIN number which leads to a rejection multiple times, again when they try with correct code they can successfully register to the site. When I pull the data to see if anyone still had issues registering I send out instructions email. (Though it’s already available in their welcome kit). Now this is a daily process which is taking longer. Right now am sorting with employee code , giving vlook ups , conditional formatting by highlighting the duplicate ee codes and checking if they successfully registered in their 2nd or 3 rd attempt and if the status is successful then No action. Though they tried 4 times still they could not register then I send out instruction email.
Great video, Chandeep! When you are adding a column can you refer to the result in the previous row in the column being added and use it as part of the formula to get the result in the current row?
Let's assume that I have a date colonm also in the table at the beginning. And ı want to calculate percentage by using previous value. How to do that ?
Playing around with this idea tried Table.SelecRows, not really better as Table.AddColumn(Custom2, "Table2", each let TableOne = [Table1] , TableThree = Table.AddColumn( TableOne , "new", each Table.SelectRows(TableOne, (B)=> B[Index] = _ [Index] -1 ) ) in TableThree) You then remove Cols, so you have a table containing a table and then just select values.
This is awesome! Besides if the data table changes to this: Date Category Card No Value 01/01/2022 A 1 125 02/01/2022 A 1 106 31/01/2022 A 1 180 04/04/2022 A 1 121 30/04/2022 A 1 101 01/01/2022 B 2 120 31/01/2022 B 2 150 04/04/2022 B 2 114 30/04/2022 B 2 154 01/01/2022 C 3 169 02/01/2022 C 3 149 31/01/2022 C 3 148 Is it possible to come up with this output? Date Category Card No Value Opening Value Closing Value Change 31/01/2022 A 1 125 125 180 55 30/04/2022 A 1 121 121 101 -20 31/01/2022 B 2 120 120 150 30 30/04/2022 B 2 114 114 154 40 31/01/2022 C 3 169 169 148 -21 Tried to use the same approach but couldn't figure out a solution. Help!
Awesome video. Thank you. Would you say it's better to make the change in Power Query or is it better to use Calculated Columns? When the tables start to get bigger I find it's better to use Calculated Columns, but I can't a decent CALCULATE (MAX ... ( FILTER formula to do the same as what you did in the Power Query. I did find ones where they use increasing values eg. MAX, or ones where they have the date as an Index, but they don't account for tables with incomplete dates. I'm still going to incorporate the Power Query Solutions, was just wondering if there was going to be issue on bigger tables.
Martin, I have learnt that pre-calculation is the key to speed and performance in PowerBI You have to balance a trade off between model refresh speed, model size and performance and then decide whether you want to perform the calculation in Power Query or DAX. There isn't a straight answer to this. It depends case to case! To more you get stuck and the more you solve the better you'll become :)
Hey @Chandeep, I have a requirement, that I need to create a column that will give value only for 1st and last row/index of a category and others with null. can you please suggest? Thank you
Good video but so much trouble and effort. This would be so easy using TSQL. My work is moving from SQL server based CRM to D365 and learning M code seems to be the only way to manipulate data now. I'm dreading it....case sensitive, list of a list etc.....all so finicky. I'd say it's this way as Microsoft has to pander to the GUI users and thus makes it so complex (relatively) for script writers
Hi Chandeep, I've been using Power BI for almost a year and watching your videos for quite some time now. I'm interested in your 4 in 1 bundle course, I only have a question though, is the training a live session or is it recorded training video? If it's a recorded video how long will be the access? Thank you!
Hey Jeffrey! The live training session happens each quarter. The current LIVE training session has already started. Perhaps you can join the next one. The 4-1 bundle is a pre-recorded course and you get the access for a life time. Hope this helps Thanks Chandeep
Let say, my data include [index] 0 to 100 and [Price], I want to refer Price value in Index 0 for all remaining row, I tried replicating your code with change ---> However, it informed error value. Please help me correct this.
hi Chandeep, we already have the pre Value, but how do we substrate in the subgroup? I tried with another custom column, by adding AllDataTable[Value] - AllDataTable[PreValue], however it is giving me an Error even with no syntax error detected, any example with subtraction to the previous row in subgroup?
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
Dude, you can´t even imagine how much this helped me. I´ve been trying to do this for 5 days with no results. You are my new best friend
I have been watching you videos since I accidentally found your channel and became you fan instantly. This is another one video that I really appreciate. Thank you very much.
Welcome aboard!
My dude. You just saved me a world of pain in dealing with a huge combined dataset. Thank you so much.
Thank you, the way you solved this issue is very elegant while teaching us how to think about the M language
I really enjoy watching and learning your lesson.
Awesome, thank you!
The best tutorial i have seen about this topic. Thanks!
Glad you like it 💚
Coming from cognos and using lots of sql, this seems like the best method i have seen so far! thanks for the help
Hi Chandeep! Thanks for another great solution. Your understanding of M code never ceases to impress me.
Thank You George!
There is lot easier solution to this as below. Still appreciate your work.
1. Just creat 2 index columns one starting with 0 and other with 1.
2. Do a self left join selecting category col and 1st index col" to "category col and 2nd index col"
3. Expand value
Boom done🎉
Rather than applying a merge.. I'd prefer the Power Query solution here - ruclips.net/video/a7O2UlbTAvA/видео.html
Merges can be expensive on large tables.
Man you are about to save my life!
Hehe.. everyone suffers from this once in a life time
Many thanks for this helpful video! I have been able to adapt its methods to creating a series of lagged values for thousands of products, in order to perform time series analyses. This method is very, very much faster to execute than my previous crude method of Merging the same table to itself over and over. (And by the way, ALL of your videos are similarly enlightening.)
Glad it helped!
Truly excellent tutorial, exactly what I needed.
Thank you! This was exactly what I needed!!!! Great content as always!
Thanks!, it really alleviated my jobs 👍
Amazing thank you for always providing great explanations for complex problems!!
Glad you like them!
Wonderful solution, Table.combine is last steps are excellent. !!
Awesome Chandeep! Love the way you transform with M vs. many multiple steps through the UI. A great learning experience! Thanks for sharing your solution and for hosting the challenge :)) Thumbs up!!
Thank you very much, Chandeep. This explanation and the logic helped me solve and unblock my issue :)
Happy to help
This is super helpful Chandeep. Not just the video and technique but also the way you explain all the aspects of the code.
Keep rocking🎉
It's my pleasure!
Really really good material. Thank you for sharing and putting your time to it.
Glad you enjoyed it!
Superb stuff. I would have got halfway but nowhere near your solution. This video will be saved for future reference and practice. Thanks Goodly 😀
Glad it helped
Thanks a lot for this video. You are a life saver!!!
Glad it helped!
awesome work mate, absolutely worked for me, 5 stars for you
Great tip bro, thank you 🙏
Good stuff! Useful info and you have excellent presentation skills. Thanks. Going to check out the courses.
Awesome, thank you!
Your M coding skill is above everything. Loved you M course too =) hopefully you are going to put maybe more section or two there =)
Yes sir I will
Very useful!
Amazing solution 👍🏻 Mind blown
You helped me so much! Thanks!!!
Glad it was helpful!
Awesome!!! Legit! Solid! Cool!!! 🤜💥🤛👏👏👏
Thanks for the video. It would be good if you could start with showing how to refer to previous row value in a single table, and then move to show how to refer to previous row value in nested tables. I think it would make it easier for the audiences to understand and master the technique step by step. Nevertheless, I have learnt a lot watching your videos 😀
"This video proves to be quite useful. Could you kindly consider creating another video that demonstrates the process of performing Power Query steps for calculating a running total at the category level?"
Buddy this was a game-changer for me.
Damn, thanks a lot bro it worked and I can brag about this in my organisation :D
Glad it helped!
Thanks for the Video - It is very helpful - what if I need to get a specific Order or Index within the group based on other fields - For example what if I want to rollup to another field called step that isn't in consecutive order for example step 2, 3a, 3b, 4, 5a, 5b, 6a, 6b, 6c, 6d, 7. I would need the index to be dynamic because there would be another field that I would filter by that would mean not all steps would be included in the result - so that when filtered it might include 2, 3a, 4, 5b, 6c, 6d and 7. but would still need to be ordered within the grouping category and the previous value would only come from the steps provided. the amount field would need to rollup. Hopefully this makes sense.
same here
Great Video Chandeep, got to this video when i was stuck on a problem to group and apply some calculation. this helped in a great extent. One more question for me to succeed. Once i capture the sub table and use table.addcolumn to create new column as you explained, is it possible to apply If statements to it. For ex. if the captured table has multiple columns and i want to compare the value of 2 of the columns which decides the value of new column. Thanks in advance for any insights
Holy shit, you just saved me a week of back and forth
Nice Video Chandeep..Instead of using Power Query, Is there a way that the same scenario can be done using DAX ..Appreciate the help
Thanks!
You bet!
hi Chandeep. Excellent tutorial. Thanks. Just a question about the intermdiatery step of creating 'AllDataTable'. Can you created an added PrevValue coumn direct to the FullTable rather than using the AllDataTable' step?? Is there difficulty in writing the M formula? Curious to know why you did not create the previous column direct in addition the existing table. Thanks in advance
I have same question
saved me yet again, chandeep!
Glad it was helpful!
@@GoodlyChandeep i have a question from this video, though. I was trying to eliminate the following step of creating a new column subtracting Previous Value from Current Value. In the Create Custom Column step I was trying to return each (AllDataTable[Value] - AllDataTable[Value]{[Index] - 1 } and it was throwing an error for all rows. Why is this?
Here's the code I used in the Added Custom Column interface.
let
AllDataTable = [all_data],
PreRowValue = Table.AddColumn([all_data], "PrevValue", each try (AllDataTable[Value] - AllDataTable[Value]{[Index] - 1}) otherwise null)
in
PreRowValue
Hi Very good!!!. But if is not the previous row. Is a specific row based on a criteria of itens \ wharehouse and for some columns instead the complete row
Hi Chandeep
This is so nice
Thanks alot
Let me ask you, is it possible to make a video on Refer Previous Row for Subgroup using DAX for performance aspects
Best regards,
Thank you for the video… I have a scenario . Our employees register in a website and sometimes they enter wrong PIN number which leads to a rejection multiple times, again when they try with correct code they can successfully register to the site.
When I pull the data to see if anyone still had issues registering I send out instructions email. (Though it’s already available in their welcome kit). Now this is a daily process which is taking longer.
Right now am sorting with employee code , giving vlook ups , conditional formatting by highlighting the duplicate ee codes and checking if they successfully registered in their 2nd or 3 rd attempt and if the status is successful then No action. Though they tried 4 times still they could not register then I send out instruction email.
man. this is legit
How do you sort your data if you have dates as well, not just category and the values?
Thank you very much
Awesome work
💚
Thanks brother
Thanks for that tutorial, but why used the M-code function to get index operation instead of the interface
Great video, Chandeep!
When you are adding a column can you refer to the result in the previous row in the column being added and use it as part of the formula to get the result in the current row?
If with category I have to consider dates also then how to do
Nice video. can we apply this for multiple category columns with grouping the values based on categories
SAVED MY LIFE
Bedankt
Thank you for the tip !
Let's assume that I have a date colonm also in the table at the beginning. And ı want to calculate percentage by using previous value. How to do that ?
When we need this prev row value in daily uses?
How to check a condition while fetching previous value? As suppose if the year is not the last year.. Then it should give null value.
great!
Great
Playing around with this idea tried Table.SelecRows, not really better as
Table.AddColumn(Custom2, "Table2", each let
TableOne = [Table1] ,
TableThree = Table.AddColumn( TableOne , "new", each
Table.SelectRows(TableOne, (B)=> B[Index] = _ [Index] -1 ) )
in
TableThree)
You then remove Cols, so you have a table containing a table and then just select values.
This is awesome! Besides if the data table changes to this:
Date Category Card No Value
01/01/2022 A 1 125
02/01/2022 A 1 106
31/01/2022 A 1 180
04/04/2022 A 1 121
30/04/2022 A 1 101
01/01/2022 B 2 120
31/01/2022 B 2 150
04/04/2022 B 2 114
30/04/2022 B 2 154
01/01/2022 C 3 169
02/01/2022 C 3 149
31/01/2022 C 3 148
Is it possible to come up with this output?
Date Category Card No Value Opening Value Closing Value Change
31/01/2022 A 1 125 125 180 55
30/04/2022 A 1 121 121 101 -20
31/01/2022 B 2 120 120 150 30
30/04/2022 B 2 114 114 154 40
31/01/2022 C 3 169 169 148 -21
Tried to use the same approach but couldn't figure out a solution. Help!
Hello sir it is possible to get slope function in power query
Awesome video. Thank you.
Would you say it's better to make the change in Power Query or is it better to use Calculated Columns?
When the tables start to get bigger I find it's better to use Calculated Columns, but I can't a decent CALCULATE (MAX ... ( FILTER formula to do the same as what you did in the Power Query.
I did find ones where they use increasing values eg. MAX, or ones where they have the date as an Index, but they don't account for tables with incomplete dates.
I'm still going to incorporate the Power Query Solutions, was just wondering if there was going to be issue on bigger tables.
Martin,
I have learnt that pre-calculation is the key to speed and performance in PowerBI
You have to balance a trade off between model refresh speed, model size and performance and then decide whether you want to perform the calculation in Power Query or DAX.
There isn't a straight answer to this.
It depends case to case! To more you get stuck and the more you solve the better you'll become :)
In general I wouldn't recommend to create calculated columns!
@@GoodlyChandeep
Awesome! What you gain on the swings you lose on the roundabouts I guess.
😬
Thanks for reply and insight.
Have a good one.
Hey @Chandeep, I have a requirement, that I need to create a column that will give value only for 1st and last row/index of a category and others with null. can you please suggest? Thank you
Good video but so much trouble and effort. This would be so easy using TSQL.
My work is moving from SQL server based CRM to D365 and learning M code seems to be the only way to manipulate data now. I'm dreading it....case sensitive, list of a list etc.....all so finicky.
I'd say it's this way as Microsoft has to pander to the GUI users and thus makes it so complex (relatively) for script writers
Hi Chandeep, I've been using Power BI for almost a year and watching your videos for quite some time now. I'm interested in your 4 in 1 bundle course, I only have a question though, is the training a live session or is it recorded training video? If it's a recorded video how long will be the access? Thank you!
Hey Jeffrey!
The live training session happens each quarter. The current LIVE training session has already started. Perhaps you can join the next one.
The 4-1 bundle is a pre-recorded course and you get the access for a life time.
Hope this helps
Thanks
Chandeep
Sir, pls guide how to do same by DAX
This doesn't work for me. It just receives current index value rather than previous despite putting in the -1
Chandeep! Is this viable with 18 million rows?
No this would crash. I'd recommend a modeling approach rather than a PQ approach.
I've discussed that here - ruclips.net/video/UPddzZnsf5w/видео.html
Let say, my data include [index] 0 to 100 and [Price], I want to refer Price value in Index 0 for all remaining row, I tried replicating your code with change ---> However, it informed error value. Please help me correct this.
What if I want to get next row instead of previous row
I am having 70K+ rows in Query table, it taking more time and often getting hanged. Any other solution please.
how to add not one, but several columns?
hi Chandeep, we already have the pre Value, but how do we substrate in the subgroup? I tried with another custom column, by adding AllDataTable[Value] - AllDataTable[PreValue], however it is giving me an Error even with no syntax error detected, any example with subtraction to the previous row in subgroup?
I realized the error was due to [PreValue] has data type Table, how do I change to number in the command?
I figured out the issue, syntax should be AllDataTable[Min] {[Index]} - AllDataTable[PreValue]{[Index]
This is insane
This is very complex...Try for some easy solution
Way to complicated. It should be doable through the UI
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/