ฝัง
- เผยแพร่เมื่อ 28 มิ.ย. 2024
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
- - - - My Courses - - - -
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
- - - - Solution File - - - -
www.goodly.co.in/refer-previe...
---
► Artist Attribution
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
- Source: goo.gl/fh3rEJ
Official After The Fall TH-cam Channel Below
th-cam.com/channels/GQE.html...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses - วิทยาศาสตร์และเทคโนโลยี
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
My dude. You just saved me a world of pain in dealing with a huge combined dataset. Thank you so much.
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!
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, the way you solved this issue is very elegant while teaching us how to think about the M language
Truly excellent tutorial, exactly what I needed.
Coming from cognos and using lots of sql, this seems like the best method i have seen so far! thanks for the help
Thank you! This was exactly what I needed!!!! Great content as always!
The best tutorial i have seen about this topic. Thanks!
Glad you like it 💚
Man you are about to save my life!
Hehe.. everyone suffers from this once in a life time
I really enjoy watching and learning your lesson.
Awesome, thank you!
awesome work mate, absolutely worked for me, 5 stars for you
Wonderful solution, Table.combine is last steps are excellent. !!
Thanks!, it really alleviated my jobs 👍
Amazing solution 👍🏻 Mind blown
Awesome!!! Legit! Solid! Cool!!! 🤜💥🤛👏👏👏
Amazing thank you for always providing great explanations for complex problems!!
Glad you like them!
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 - th-cam.com/video/a7O2UlbTAvA/w-d-xo.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.)
Glad it helped!
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?
Good stuff! Useful info and you have excellent presentation skills. Thanks. Going to check out the courses.
Awesome, thank you!
Great tip bro, thank you 🙏
Hi Chandeep! Thanks for another great solution. Your understanding of M code never ceases to impress me.
Thank You George!
Nice video. can we apply this for multiple category columns with grouping the values based on categories
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!
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
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
Thank you very much, Chandeep. This explanation and the logic helped me solve and unblock my issue :)
Happy to help
Thanks a lot for this video. You are a life saver!!!
Glad it helped!
You helped me so much! Thanks!!!
Glad it was helpful!
"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 brother
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.
How do you sort your data if you have dates as well, not just category and the values?
Thank you very much
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 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 😀
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,
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
Holy shit, you just saved me a week of back and forth
Thanks!
You bet!
great!
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
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
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 ?
Thanks for that tutorial, but why used the M-code function to get index operation instead of the interface
Great
SAVED MY LIFE
Awesome work
💚
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.
man. this is legit
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
Hello sir it is possible to get slope function in power query
Nice Video Chandeep..Instead of using Power Query, Is there a way that the same scenario can be done using DAX ..Appreciate the help
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.
When we need this prev row value in daily uses?
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
Bedankt
Thank you for the tip !
Sir, pls guide how to do same by DAX
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.
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
This is insane
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!
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 - th-cam.com/video/UPddzZnsf5w/w-d-xo.html
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]
What if I want to get next row instead of previous row
how to add not one, but several columns?
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.
I am having 70K+ rows in Query table, it taking more time and often getting hanged. Any other solution please.
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
This is very complex...Try for some easy solution
Way to complicated. It should be doable through the UI