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.
@@BIGorilla I also have dates that need to be sorted. However, where adding the extra column with the index the date column looses its format, then gets unsorted and hence the indexes doesn't fit my purpose. Is there a way to avoid losing format of the column date while adding the extra column? Thank you!
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!
Wow amazing 🤩 I searched 2 days for the same concept in RUclips 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
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.
Dude, I've reached out your website and decided to come here just to say: YOU ARE A F*CKING GENIUS!! This was SO helpful!! I was almost putting a python/R code for do that and you bring the most smartest solution EVER ! Thanks a lot!!!
Thanks for your great explanation and for sharing all these videos. I have watched both videos about creating indexes for the subgroups (multilevel hierarchy), but I couldn't figure out how to integrate List.FirstN (running totals) into such subgroups. I'd appreciate it if you could extend your Running Totals video for multilevel hierarchy as in this video. Thanks again
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!
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.
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!
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].
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)}} )
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!
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 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
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.
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.
Hi there, I have something similar, after the self join on multiple classifications, it seems powerquery actually reindexes the table as part of the process, and Index with Index 1, becomes the last one (n), then number 2 becomes 1, 3 becomes 2 etc. The previous index is also adjusted. The end order is date 2, date 3, date 4, .... date 1. Everything is out of sequence. .
Hey Ayman, In Power Query you can find the View tab. Out there is a section called Data preview. You can select things like: Column quality, column distribution and Column Profile to get the summary graphic in each column. Its a fun feature! Cheers, Rick
Hi Arthur, That's off topic for this video. But I'm assuming you're referring to the video about summing Null values. If so, you can replace nulls with 0 as well. It's one of the methods provided. However, if you find the performance is less than ideal, you could look into the other methods I show. They may prove quicker and better optimized. That could be relevant with big datasets. Hope that helps! Rick
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.
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.
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
![Quando Rondo - Life Goes On [Official Music Video]](http://i.ytimg.com/vi/eIf3fhMXAow/mqdefault.jpg)
To learn more about functions and syntax make sure to check out: powerquery.how/
Enjoy!
There are 4 different videos on youtube explaning the same solution with almost same methodology. But yours is the best. Thank you
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.
Good good!! I didn't add it in the video, but sorting can be important to keep the right order for your data. Well solved 😁
@@BIGorilla I also have dates that need to be sorted. However, where adding the extra column with the index the date column looses its format, then gets unsorted and hence the indexes doesn't fit my purpose. Is there a way to avoid losing format of the column date while adding the extra column?
Thank you!
@@jesuslax5444 Did you group straight after sorting? This step keeps the dates flowing sequentially
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!
You deserve more views. This is extremely helpful, Thanks!
Andres! That's too kind. I'm happy it helped, share the knowledge 😎
Rick
You're a G! Spent days looking for this. I will now create a custom function that can run this automatically.
I have been looking for this solution for a long time. Your video was clear and easy to follow. Thank you sooo much!
Wow amazing 🤩 I searched 2 days for the same concept in RUclips 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
Thank you!!
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 spent nearly 2-3 hours to resolve this using DAX. you're amazing 😍
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!
Excellent step-by-step instructions!
I can't thank you enough.
That is fantastic to hear Bruno, and a cheer to you for picking up the steps. Enjoy!
This helped me finally sort out an issue I was struggling with, thank you so much!
Thank you, I used this to add the value from the cell below in case a line in csv is broken into two lines.
Thank you so much for this explanation. This is just what I needed to complete a project.
Dude, I've reached out your website and decided to come here just to say: YOU ARE A F*CKING GENIUS!! This was SO helpful!! I was almost putting a python/R code for do that and you bring the most smartest solution EVER ! Thanks a lot!!!
Muchas gracias, Explicacion simple y concreta
Thanks for your great explanation and for sharing all these videos. I have watched both videos about creating indexes for the subgroups (multilevel hierarchy), but I couldn't figure out how to integrate List.FirstN (running totals) into such subgroups. I'd appreciate it if you could extend your Running Totals video for multilevel hierarchy as in this video.
Thanks again
Such a bless to find a way to perform a lag function
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!
This should be attached to the top! Thanks a lot!
this saved my career. Thanks a lot!!
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.
This was great and extremely helpful to some work I'm doing right now. Thank you for posting!!
Great! glad it helped!
Great explanation, effective and elegant. Thanks for sharing
Very well explained and quite "easy" solution. Really useful!
That's great, thanks Alan!
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.
You just saved a life. Thank you very much.
Thanks a lot, your video save me. hugs from Colombia
I've been searching for more than a week . Thanks a lot.Amazing !!
For some reason when I expand the merge, the index column becomes incorrect. The Index column was correct before expanding the merge.
your my favorit if it comes to power query thank you very much
Great to see you here in the channel René. More coming soon!
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!
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].
Perfect! Justo lo que necesitaba.
You just save my day. Thanks for at great video
Excellent tip..... thank you
Each of your videos is excellent. Thanks for sharing 🌹👍
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)}} )
I learned a lot from this, thanks.
That's fantastic fr1sket, and thanks for dropping a comment :)
Great video! Thank you Sir!
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
In my project i needed to add ....Table.Buffer(Table.Sort( before the sorting on the first sort to get the right result (tip from imke feldmann)
Thanks for this!! Super helpful!
Enjoy!
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!
Excellent Content 👍
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?
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.
Always a pleasure,so is it more convenient to use this calculus in Pq or use the similar formulas in Dax ?
Thank you!!!
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
Awesome!!
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.
The best
Just spreading the knowledge ! 😊
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.
Hi there, I have something similar, after the self join on multiple classifications, it seems powerquery actually reindexes the table as part of the process, and Index with Index 1, becomes the last one (n), then number 2 becomes 1, 3 becomes 2 etc. The previous index is also adjusted. The end order is date 2, date 3, date 4, .... date 1. Everything is out of sequence. .
Very useful!
My pleasure ;)
Is there an equivalent to CTE recursive in Power Query ?
How did you get that first row that has the summary graphic
Hey Ayman,
In Power Query you can find the View tab. Out there is a section called Data preview. You can select things like: Column quality, column distribution and Column Profile to get the summary graphic in each column.
Its a fun feature!
Cheers,
Rick
@@BIGorilla Thanks so much Rick, this is really helpful! Glad I found you :)
That's bananas!
Ive been pulling my hair out for two days to try to solve exactly this problem. I cant wait to go to work tomorrow 🤣
Clear explanation. However, why not just replace the null value with zero (0) before performing the subtraction?
Hi Arthur,
That's off topic for this video. But I'm assuming you're referring to the video about summing Null values.
If so, you can replace nulls with 0 as well. It's one of the methods provided. However, if you find the performance is less than ideal, you could look into the other methods I show. They may prove quicker and better optimized.
That could be relevant with big datasets.
Hope that helps!
Rick
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?
Hi Priyanka. I'm not sure I understand your question, please clarify.
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.
Damn
Buckle your seatbelts! ;-)
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'm sorry, I don't understand your question. Please elaborate your question :)
I love you 😳
I love you too. Let's get married 😂
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