To learn more about functions and syntax make sure to check out: powerquery.how/ Enjoy!
@cujasa383 жыл бұрын
You deserve more views. This is extremely helpful, Thanks!
@BIGorilla3 жыл бұрын
Andres! That's too kind. I'm happy it helped, share the knowledge 😎 Rick
@brunoromano9772 жыл бұрын
Excellent step-by-step instructions! I can't thank you enough.
@BIGorilla2 жыл бұрын
That is fantastic to hear Bruno, and a cheer to you for picking up the steps. Enjoy!
@lilianaacosta96652 жыл бұрын
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.
@BIGorilla2 жыл бұрын
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 😁
@jesuslax54442 жыл бұрын
@@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!
@stevenstanford8618 Жыл бұрын
@@jesuslax5444 Did you group straight after sorting? This step keeps the dates flowing sequentially
@vacilando862 жыл бұрын
There are 4 different videos on youtube explaning the same solution with almost same methodology. But yours is the best. Thank you
@msm0231322 жыл бұрын
I have been looking for this solution for a long time. Your video was clear and easy to follow. Thank you sooo much!
@StreetsHQ9 ай бұрын
You're a G! Spent days looking for this. I will now create a custom function that can run this automatically.
@oscararmandocisnerosruvalc8503 Жыл бұрын
Such a bless to find a way to perform a lag function
@controloverreports11 ай бұрын
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
@controloverreports10 ай бұрын
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)
@federicoulloa5184 Жыл бұрын
Muchas gracias, Explicacion simple y concreta
@lecollabdul Жыл бұрын
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!
@pablo4917 ай бұрын
Perfect! Justo lo que necesitaba.
@friahiemstra293711 ай бұрын
This helped me finally sort out an issue I was struggling with, thank you so much!
@reggieo4889 Жыл бұрын
Thank you so much for this explanation. This is just what I needed to complete a project.
@tho030492 жыл бұрын
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!
@IslamWay_802 жыл бұрын
I've been searching for more than a week . Thanks a lot.Amazing !!
@Naveen_dronphy3 жыл бұрын
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
@BIGorilla3 жыл бұрын
Thank you!!
@roycemekolle7334 Жыл бұрын
You just saved a life. Thank you very much.
@Alan.DL73 жыл бұрын
Very well explained and quite "easy" solution. Really useful!
@BIGorilla3 жыл бұрын
That's great, thanks Alan!
@lamermusicmn3 жыл бұрын
This was great and extremely helpful to some work I'm doing right now. Thank you for posting!!
@BIGorilla2 жыл бұрын
Great! glad it helped!
@diegolozano23972 жыл бұрын
Thanks a lot, your video save me. hugs from Colombia
@isidrebague36592 жыл бұрын
Great explanation, effective and elegant. Thanks for sharing
@terrydouglasjayasuriya51622 жыл бұрын
Excellent tip..... thank you
@PranayPawar19912 жыл бұрын
I spent nearly 2-3 hours to resolve this using DAX. you're amazing 😍
@kuuuyajim10 ай бұрын
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.
@fr1sket3633 жыл бұрын
I learned a lot from this, thanks.
@BIGorilla2 жыл бұрын
That's fantastic fr1sket, and thanks for dropping a comment :)
@jeromeastier462 Жыл бұрын
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.
@reneglaus24763 жыл бұрын
your my favorit if it comes to power query thank you very much
@nkcf48893 жыл бұрын
Thanks for this!! Super helpful!
@BIGorilla3 жыл бұрын
Enjoy!
@henrikguldborg80082 жыл бұрын
You just save my day. Thanks for at great video
@kebincui11 ай бұрын
Each of your videos is excellent. Thanks for sharing 🌹👍
@ioannisnerantzakis42342 жыл бұрын
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!
@ChocoHammer Жыл бұрын
This should be attached to the top! Thanks a lot!
@kelvinalmarez54953 ай бұрын
this saved my career. Thanks a lot!!
@piggyware Жыл бұрын
Thank you, I used this to add the value from the cell below in case a line in csv is broken into two lines.
@gyanchintan3 ай бұрын
Excellent Content 👍
@catalinajaramillo8516 Жыл бұрын
Thank you!!!
@leegarrett19082 жыл бұрын
Great video! Thank you Sir!
@williamarthur4801 Жыл бұрын
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)}} )
@famillepreira8422 Жыл бұрын
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.
@paulshoup433825 күн бұрын
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].
@aleksanderstrommer76772 жыл бұрын
That's bananas!
@Adamowicz1233 жыл бұрын
The best
@marcbeaton2107 Жыл бұрын
Ive been pulling my hair out for two days to try to solve exactly this problem. I cant wait to go to work tomorrow 🤣
@RameezMannil2 жыл бұрын
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!
@arturoramirez7122 жыл бұрын
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!
@jamespease71806 ай бұрын
For some reason when I expand the merge, the index column becomes incorrect. The Index column was correct before expanding the merge.
@sarajvo20103 жыл бұрын
Very useful!
@BIGorilla3 жыл бұрын
My pleasure ;)
@GustavoLimaBrasil4 ай бұрын
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.
@paulstaggers581110 ай бұрын
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?
@mecorowa56426 ай бұрын
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.
@bahlatbilal Жыл бұрын
Always a pleasure,so is it more convenient to use this calculus in Pq or use the similar formulas in Dax ?
@hosseintafaghodi394 Жыл бұрын
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
@DinoAMAntunes2 жыл бұрын
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
@williamarthur4801 Жыл бұрын
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.
@mattmatt245 Жыл бұрын
Is there an equivalent to CTE recursive in Power Query ?
@aymanraouf18202 жыл бұрын
How did you get that first row that has the summary graphic
@BIGorilla2 жыл бұрын
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
@klamentyne59912 жыл бұрын
@@BIGorilla Thanks so much Rick, this is really helpful! Glad I found you :)
@artrothberg2 жыл бұрын
Clear explanation. However, why not just replace the null value with zero (0) before performing the subtraction?
@BIGorilla2 жыл бұрын
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
@aymanraouf18202 жыл бұрын
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.
@priyankaroy34943 жыл бұрын
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?
@BIGorilla3 жыл бұрын
Hi Priyanka. I'm not sure I understand your question, please clarify.
@JOTAGEPEME3 жыл бұрын
Damn
@BIGorilla2 жыл бұрын
Buckle your seatbelts! ;-)
@patilkv20122 жыл бұрын
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.
@BIGorilla2 жыл бұрын
I'm sorry, I don't understand your question. Please elaborate your question :)
@carlosarthurscapinjordaoco8698Ай бұрын
I love you 😳
@BIGorillaАй бұрын
I love you too. Let's get married 😂
@johno1104 Жыл бұрын
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