To learn more about functions and syntax make sure to check out: powerquery.how/ Enjoy!

You deserve more views. This is extremely helpful, Thanks!

Excellent step-by-step instructions! I can't thank you enough.

Love you!! Just what I needed!! I did a little modification to accomodate to my data and worked perfectly. I first sorted by dates before grouping and then followed the steps you shared.

There are 4 different videos on youtube explaning the same solution with almost same methodology. But yours is the best. Thank you

I have been looking for this solution for a long time. Your video was clear and easy to follow. Thank you sooo much!

You're a G! Spent days looking for this. I will now create a custom function that can run this automatically.

Such a bless to find a way to perform a lag function

NIce one Rick. Just what I was looking for today. I knew I had seen it somewhere on your youtube. Keep up the good work

Muchas gracias, Explicacion simple y concreta

Great Video! helps me save a lot of extra steps I had to do in a report that has to be updated daily. Thank you!

Perfect! Justo lo que necesitaba.

This helped me finally sort out an issue I was struggling with, thank you so much!

Thank you so much for this explanation. This is just what I needed to complete a project.

Thank you so much for this! Solved an issue partially with this video... It's like piecing together a puzzle. It's almost impossible to find one video that solves a problem, but together we make it happen. Thanks for making it happen!

I've been searching for more than a week . Thanks a lot.Amazing !!

Wow amazing 🤩 I searched 2 days for the same concept in KZfaq finally it’s here wooo easily understood thumbs up 👍🏻 I felt doing substraction , multiply was difficult in power bi but your video amazing 🤩 pls keep sharing

You just saved a life. Thank you very much.

Very well explained and quite "easy" solution. Really useful!

This was great and extremely helpful to some work I'm doing right now. Thank you for posting!!

Thanks a lot, your video save me. hugs from Colombia

Great explanation, effective and elegant. Thanks for sharing

Excellent tip..... thank you

I spent nearly 2-3 hours to resolve this using DAX. you're amazing 😍

This partially solves the issue I have for repeat calls calculation but will have to test performance in dataflow or in dataset refresh since my data is more than a million rows. Thank you.

I learned a lot from this, thanks.

Great! i will use it to calculate the difference between two periods of Balance sheet accounts that will be used in a Cash Flow Statement.

your my favorit if it comes to power query thank you very much

Thanks for this!! Super helpful!

You just save my day. Thanks for at great video

Each of your videos is excellent. Thanks for sharing 🌹👍

Hi, very helpful video and great presentation style, thank you! I would propose 1 change. I added another Custom column after the Details Custom Column and before the DetailsIndex to sort each table as below: Name: DetailsSort Formula: Table.Sort( [Details], {"Date", Order.Ascending}) Remember you will also need to amend the DetailsIndex Formula: Table.AddIndexColumn( [DetailsSort],1,1,Int64.Type) The benefit is that after the expansion of the table, the sort is not disturbed (as mentioned from some people in the comments). I had the same problem and this fixed it. Please share if there is any better way to achieve that. Thanks!

Thank you, I used this to add the value from the cell below in case a line in csv is broken into two lines.

Excellent Content 👍

Thank you!!!

Great video! Thank you Sir!

Great video, really liked the trick of just putting curly braces around a columns name in list.sum, also the way of adding index to a group, what I usually do is just remove everything after each in the formula bar and replace it with addindex ; {{"Count", each Table.AddIndexColumn(_, "Index", 1,1, Int64.Type)}} )

Hello Rick, Thank you for this awesome video. I have a challenge that I would love you to help me with. I feel that this video is helping but I need to take it to the next level :) I have items that are being ordered monthly. Before injecting the items into my system, I need to make sure that the monthly order respect a certain minimum quantity (MOQ). Lets say item A is wanted in 01/01/2023 - 80000 pcs in 01/02/2023 - 40000 pcs and in 01/03/2023 - 80000 pcs. The problem is that the MOQ of this item is 150000 pcs. So since the monthly orders does not respect the minimum qty, I add up February and January so it is 120000 pcs. It is still under the MOQ. So I add up to that the march need. So it is 200000 pcs that I will have to inject into my system in 01.01.2023. The tricky part is that I should tell Query that if the MOQ is respected, then go to the next line, but if it is not, the add up the quantity below but leave it in the first required date. And adding up a little spices to that 😊, same exemple but with a 4th PO wanted in 01.04.2022 for 150000 pcs. Query should be able to return that the need for January is 200 K AND the next need is for April for 150K. (Since the MOQ is respected, there is no need to add up this need with the others). I hope I am clear. I did not find a solution for this request. Will really appreciate your help on this. Thanks a lot.

The last video I watched to do this FROZE my power BI. YOU made this so simple. Do you have a video that will ONLY refresh new data? I tried to follow other videos with no luck. I want to only refresh the new data, without having to refresh all the data, and freezing my computer for 10 minutes [millions of lines of data].

That's bananas!

The best

Ive been pulling my hair out for two days to try to solve exactly this problem. I cant wait to go to work tomorrow 🤣

Neat! I wanted to display the latest approval status of document revision, if the latest submission is not responded to. Guess I was searching for this. Need to try!

Does this solution work if the previous (or next record) is a text field? All examples I've seen deal with a numeric field. For some reason after I join the table to itself my index numbers shift and my row 1 no longer has the first index number. Row 1 has the first index number before I expand the joined table but not after. Strange!

For some reason when I expand the merge, the index column becomes incorrect. The Index column was correct before expanding the merge.

Very useful!

what would you suggest for a table with 550k row which is taking 01 hour to do this? Great video by the way as I manage, as a basic user, do that.

Working on some data (auto insurance app telemetry) that looks like this: 28-Sep 12:08 PM 20 mins 6 secs (8.17 mi) Great job! No events this trip. 26-Sep 8:43 AM 3 mins 1 sec (0.55 mi) 1 Hard brake 26-Sep 8:04 AM 1 min 15 secs (.75 mi) Great job! No events this trip. Tricky part is that there are 4 rows when no events are recorded, but 5 rows when there is an event ("Hard brake" = 1 or more). Thinking I could adapt this strategy to develop 6 columns: Date, Start Time, Trip Duration, Mileage, Alert Message, Number of Alerts. Do you have any thoughts/suggests on a good approach?

Thank you for the video and different possibilities to run this operation. Unfortunately non of the options is really fast when you have hundreds of files with millions lines. Already a file with hundred thousand lines slows terrible down. I use now a python script before to do this operation and calculations. Python does this job in seconds. It would be great to run the python script directly from excel to have it in a closed environment.

Always a pleasure,so is it more convenient to use this calculus in Pq or use the similar formulas in Dax ?

Thank you for the great video. Do you have any solution if we want to add the value of a row to the previous one? let's imagine A1=1, A2=2, A3=3, A4=4, and A5=5 I would like a new column B with values: B1=1, B2=1+2=3, B3=3+3=6, B4=6+4=10, B5=10+5=15. Thank you

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

Just come up with another way, let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Adex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), Pcol = List.RemoveLastN( {0} & Table.Column( Source, "Units" ) ), Punit = Table.AddColumn( Adex, "Preunits", each Pcol { [Index] } ) in Punit I'd liked to have not used an index column, but haven't come up with a way, did try just using it as variable but could not get it to work.

Is there an equivalent to CTE recursive in Power Query ?

How did you get that first row that has the summary graphic

Clear explanation. However, why not just replace the null value with zero (0) before performing the subtraction?

I tried your approach but I have an issue. My master data is sorted based on 4 classifications: train name, departure time, block hour, block minute. This is done through Data tab, then Sort, and I have 4 classifications for the Sort Order. But in Power Query, it doesnt recognize this sorting at all. Even my Index columns are not in order.

Can we do this for measures. I mean for example we will be having measure1 created having values value1,value2.So can we do the previous calculation?

Damn

Thanks it is good, but not working expectedly when my tasks are three. How to do if my tasks are three means I want previous value at Task1 -- Task11 -- Task111 level.

I love you 😳

Gad....so finicky where it would take 2 minutes with SQL lag, lead or row over parition by. My job has moved from SQL server database to Daverse and I'm not happy. Why does microsoft make things more difficult