Power Query Running Count Based on Row Condition - Excel Magic Trick 1588

[@ZappPSR]

Hi! I learned a lot from you about Excel. I moved to Power BI in 2015 and kind lost track of your videos. It is wonderful to know you also teach Power Query. The thing I found most amazing about the way you teach is not only your knowledge about the topic, but the creativity an logic of the construction of your functions and expressions. It really made a difference in the way I think about code and formulas. Thank you for your contribution!

[@FRANKWHITE1996]

Mike, what I would do without you? Every time I need excel/power query idea - I find you! 🙏

[@excelisfun]

Glad to help, FRANKWHITE1996 : ) : )

[@vida1719]

Alternatively index can be added within grouped table: = Table.Group(#"Changed Type", {"Product"}, {{"Grouped", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}), which needs to be expanded

[@excelisfun]

Much better way to do it! Thanks for that awesome tip from you and Bill Szysz : ) That will be tomorrow's video!

[@granand]

You don't use bufferredTable?

[@ExcelInstructor]

@@excelisfun Hi Mike Mr ExcelIsFun. today at work I struggled with something based on this video. 1. My product column is numeric (Fixed Assets numbers) 2. My transaction Column - did not have it but added index Insted . Here everything workedas intended. now the 3rd part. I had 1 more column with mixed data Containing "New:" and "Old:" and some other descriptions that are not important. What I struggled with is to do that running total not only per asset and index (as you did here) but also by either new or old text and do not count when there is different value in that3rd column (null value in such case). Any chance you could help? More info: the New / old parameter are simple info about Fixed asset change (like qty, description, values etc, any change to an asset) and Ineed to count how many changes there were to 1 specific asset.

[@pablofts1569]

i love you, Vida!

[@inteligenciadedatos9661]

Hey i like yo say tankyou, from Colombia... I needed this transformation for a data model in Power BI. from this moment I will review all your M code videos... Many thanks again.

[@excelisfun]

Glad you like my videos. Here is a video that goes over M Code basics: kzbin.info/www/bejne/hISUcqmci551m6s Here is the full playlist: kzbin.info/aero/PLrRPvpgDmw0kuIuvn2rCQI14AthSZ43ca

[@Officeinstructor]

Thank you Mike... Awesome I had to watch it 3 times before I'm able to do it

[@excelisfun]

You are welcome!!!! Practice many times and you can get good. That is how I do it too : )

[@danielhutchinson6158]

Mike. This is exactly what I needed before continuing with the 365 MECS 04. So grateful!

[@ogwalfrancis]

Power Query, the number 1 package from Microsoft, Thanks You

[@excelisfun]

Power Query IS pretty amazing!!!!! Thanks for stopping by, Ogwal!!!

[@nourreldeenmohamed367]

The legend of excel ❤❤

[@excelisfun]

This method is slow on big data. But the multiple step process can avoid the slowness: kzbin.info/www/bejne/mZLQf4CZZt-UeMU

[@williamarthur4801]

I couldn't believe it when I hit enter and it worked, thanks one again, I could have done with a bit more theory regarding 'why' especially variables and go to operator. I speak as someone relatively new to M code.

[@excelisfun]

My free classes here at KZbin set up all the theory. Here is Video #9 in the MSPTDA class that has all the theory: kzbin.info/www/bejne/hISUcqmci551m6s

[@wayneedmondson1065]

Wow Mike.. super!! Makes me appreciate the beauty and simplicity of worksheet functions, but at the same time, the pure power of M Code.. and your wizardry in using it!! Will definitely be spending some time in MSPTDA in the future. Thanks and Thumbs up!!

[@excelisfun]

You are welcome, Wayne! I agree that sometimes the Worksheet formulas are easier : )

[@jacky_23]

You taught me a lot about power query than any one book 📕 keep up the good work Sir You rock !!

[@excelisfun]

Glad to help!

[@CeliaAlvesSolveExcel]

Brilliant, Mike! As always. :)

[@excelisfun]

Always glad to help : )

[@stevecrawford1844]

You Sir, are my new favourite person! Half a day searching for an answer that I followed in less than 30 minutes.

[@excelisfun]

Glad to help : )

[@DougHExcel]

power query magic from excelisfun.....Excel-e-lent! thanks mike.

[@excelisfun]

Thanks, Doug!!!!! Glad it is EXCELlent for you : ) : ) : )

[@williamarthur4801]

Thanks for your reply, I got up to 8 in MSPTDA, and then decided to have a break and refresh of what I'd learned. 😊

[@aikox2111]

Hi! This is exactly what I need right now. Thanks a lot for this amazing video!

[@sewstud]

Super understandable explanation!🙏🙏🙏

[@fluorine121]

amazing, thousand thanks to you! Just exactly what I am looking for. Excel is flexible but to work with large data, pivot power is much stronger tool.

[@excelisfun]

Glad the video helps!

[@alexkim7270]

Brilliant. I was just thinking where do I get started with the syntax and you mentioned them right at the end. Thanks Mike.

[@excelisfun]

Yes, it is best to watch videos 3 - 12 to get the full picture of Power Query and M Code, but video #9 is the hard core M Code video: kzbin.info/aero/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1

[@excelisfun]

Please help support the free resources that I post Alex Kim, your your comments and thumbs up on each video that you watch and of course your Sub : )

[@bbibas]

You just saved my life, been searching for an answer to this problem for days, and only found complicated solutions.... Thanks!!!! My table is a little big, can i buffer only a few coluns? that will make it lighter to calculate....

[@zbatevp-vlogs610]

Ive beennlooking for this all my life!!!! hahaha. thank you very mucho!!!

[@katerina6495]

Thank you for the variety of the magnificent Excel fun Mike 🤗

[@excelisfun]

You are welcome for the variety and magnificent fun, Katerina!!!!

[@mattschoular8844]

Somehow I missed this video when it came out. Thanks for for the great explanation Mike. This solution will be useful for sure.

[@excelisfun]

Glad you found it now! But with all single cell Power Query solutions, be careful on big data sets, cuz it might be slow : (

[@matheusgameiro61]

That was exactly my problem! Thx

[@FRANKWHITE1996]

magic 🙌

[@HusseinKorish]

WHAT ???!!! ..... WOW ....i'll go directly to your MSPTDA ....thanks Mike

[@excelisfun]

MSPTDA is good : )

[@SyedMuzammilMahasanShahi]

Beautiful video. Thanks Mike for the awesomeness.

[@excelisfun]

You are welcome, Syed : ) : )

[@tomaszpietrowski8755]

Hi, extremely helpful video, thank you!

[@chrisbrindle6298]

Fantastic!

[@karolinegarcia2523]

Mike, what an amazing video!!! Thanks to sharing this.

[@excelisfun]

You are welcome, Karoline!!!

[@adamshrinah2595]

Thank you

[@gregoirebertrand5787]

Works great for small sample but takes so much time for big samples ( 160k rows)

[@excelisfun]

Yes, that is true : (

[@gregoirebertrand5787]

ExcelIsFun do you know of there is any alternative to do it on big samples ?

[@hudzaifahhudzaifah-zv6yw]

​@@gregoirebertrand5787Hi bro, did you get the alternative ways?

[@jayrodrabadon6609]

Thank you Mike!

[@johnborg5419]

Great One Mike. Beautiful Staff. :) :)

[@excelisfun]

Thanks, John Borg : ) : )

[@elissandrorosa]

Amazing, thank you!

[@Karthickchidambaram]

Fantastic Video

[@mango38796]

thank you. It is so helpful

[@MalinaC]

Amazing! PQ is so much different from Excel... And getting easier and easier with intelli sense ;)

[@excelisfun]

Yes, the intelli sense does help : )

[@granand]

Thank you very much Sir

[@donovannewton9507]

This is brilliant. Could have used this a long time ago

[@excelisfun]

Glad it is here now for you, Donovan!!!! Thanks for your support : )

[@ilovepowerbi811]

Very good, i am searching this thing

[@xszil6994]

Mind blown. Thank you!

[@excelisfun]

You are welcome, Most Awesome Excel Sister!!!!! The next video is an even better way to do this : )

[@xszil6994]

@@excelisfun OMG Can't wait to see the next one. This topic was so perfectly-quantumly timed.. was just trying to do COUNTIFS in PQ last week and now you've saved meeeeeee!!!

[@BillSzysz1]

Beautiful logic in M language :-)) Thanks.

[@excelisfun]

Thanks, Teammate, Bill "Mr Power Query" Szysz : ) : ) !!!!

[@AtakanAkarsu]

Amazing..

[@GC-lu6en]

Impressive Video

[@Tcdm1pp91]

YOu are really great, what if instead of counting rows i would need to sum them? thanks

[@janwillemvanholst]

Here is a (simple?) alternative (from the Change Type step): #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"Data", each _, type table [#"Transaction No."=nullable number, Product=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "DataWithIndex", each Table.AddIndexColumn([Data],"Index",1,1)), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"DataWithIndex"}), #"Expanded DataWithIndex" = Table.ExpandTableColumn(#"Removed Other Columns", "DataWithIndex", {"Transaction No.", "Product", "Index"}, {"Transaction No.", "Product", "Index"}), #"Sorted Rows" = Table.Sort(#"Expanded DataWithIndex",{{"Transaction No.", Order.Ascending}}) in #"Sorted Rows"

[@excelisfun]

Yes, that is great, Jan Willem!!! I added your code to download workbook. Thanks for helping out the Team : ) Here is the final code (I think we have to sort at the very beginning too): let Source = Excel.CurrentWorkbook(){[Name="ProductStartTable"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction No", Int64.Type}, {"Product", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Transaction No", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Product"}, {{"AllRows", each _, type table [Transaction No=nullable number, Product=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "RunningCount", each Table.AddIndexColumn([AllRows],"Running Total",1,1)), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"RunningCount"}), #"Expanded RunningCount" = Table.ExpandTableColumn(#"Removed Other Columns", "RunningCount", {"Transaction No", "Product", "Running Total"}, {"Transaction No", "Product", "Running Total"}), #"Sorted Rows1" = Table.Sort(#"Expanded RunningCount",{{"Transaction No", Order.Ascending}}), #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows1",{{"Transaction No", Int64.Type}, {"Running Total", Int64.Type}, {"Product", type text}}) in #"Changed Type1"

[@excelisfun]

BTW, Vida and Bill Szysz also suggested this : )

[@excelisfun]

Also, the next video, Power Query Running Count with Grouping & Add Index - Excel Magic Trick 1589, kzbin.info/www/bejne/mZLQf4CZZt-UeMU, shows your trick : )

[@sandraying5990]

Thank you so much!!!

[@mohamedchakroun4973]

Imbelivable mike :) no comment briliant

[@chrism9037]

Nice video Mike!

[@excelisfun]

Glad it is nice for you, Chris : )

[@GiteshBajaj]

Very Useful. Thx

[@excelisfun]

Glad it was helpful!

[@aahanavikram07]

Thanks for the video..

[@excelisfun]

You are welcome, aditya!!!!

[@teaking1981]

I know its its an old video but I was re watching and for fun thought I would try to do it another way with out merging and selecting rows line by line and came up with this, its more complicated but it was fun and helped with my learning: let Source = Table.FromRows( { {"1023","Quad"}, {"1024","Carlota"}, {"1025","Quad"}, {"1026","Sunshine"}, {"1027","Aspen"}, {"1028","Aspen"} }, {"ID","Product"}), LB_Transactions = List.Buffer(Source[ID]), LB_Product = List.Buffer(Source[Product]), Count = List.Count(LB_Product), R_Count = Table.FromRecords( List.Generate( ()=> [I=0,ID=LB_Transactions{0}, Product=LB_Product{0}, R_Count_List={LB_Product{0}},R_Count=1], each [I] < Count, each [ I=[I]+1, ID=LB_Transactions{[I]+1}, Product=LB_Product{[I]+1}, R_Count_List=[R_Count_List]&{LB_Product{[I]+1}}, R_Count=List.Count(List.Select([R_Count_List]&{LB_Product{[I]+1}},(R)=> R = Text.From(LB_Product{[I]+1})) ) ] ) ) in R_Count

[@nikakalichava8012]

You again mike! Thanks A LOT! one question, how d I do this in Power BI (not PQ)? Could not find any solutions on web.

[@rannufiabrito5394]

Incrível!!! Muito obrigada me ajudou muito!

[@claudiooliveira5918]

Ola, boa tarde! Consegue me explicar, estou precisando muito fazer essa formula

[@GeertDelmulle]

Beautiful as always! Thank you! Now I’m gonna predict the DAX version: - we’ll use a local variable in the formula (“to buffer the table”), - we’ll use 2 nested contexts and use the EARLIER()-function. At least: that’s the analogy I see. BTW: I can’t help but think: déjà vu. Thanks for the reminder. BTW2: You do add these videos to the right playlist, right? I mean: this one belongs in the PQ-M playlist.

[@excelisfun]

LOVE your DAX, Variable to Buffer, EARLIER - just too cool : ) Yes, I try to add them to the correct playlist, but it is just perfect timing becasue today I DID forgt to add them to the correct playlist!?!?!? You are telepathic!!! I just added them to ETM, PQ, PQ Custom Functions.

[@excelisfun]

Thanks for being such a great Teammate!!!

[@GeertDelmulle]

Mike, just like the previous time where you used these nested functions to do a little running total exercise that I requested at the time, you apply the same PQ-M logic here. That really is PQ-M on the next level, especially with that buffered table for efficiency. My PQ-M, although nicely structured in terms of queries, is usually on a previous level (one level down) from what you did here. Furthermore: your solution is just awesome because of its compactness and computational efficiency, and that in a context of “running total” (or equivalent), usually a more complex thing to compute in M or DAX. You really transcended that concept of expandable ranges from Excel Classic to the Power Tools. As a technique that is very powerful and thus useful. I’m sure we’ll be seeing this more often in the future, because it has become a new tool in our toolbox.- just my 2 cents.

[@excelisfun]

@@GeertDelmulle Thanks, Teammate Geert! Via Bill Szysz, there may be an even better way. Maybe a new video tomorrow or the next day : )

[@GeertDelmulle]

What?!? Can’t wait to see that one!...

[@xikingxblack7687]

Thank you very much for this video! But i have a little question. What if i only want to show certain columns in the ,,inside table‘‘? I have a table with over 20 columns, and i think that it would might help to improve the performance, if i only selected to column which is needed for this calculation

[@hudzaifahhudzaifah-zv6yw]

Hi Mike. Thank you for the tutorial. I have tried your step in my 23000 rows, but it take super long time to load the data. Why it happen?

[@analuisaalonsoarmas5661]

Hi, Could you help me through something like this but for SQL, the problem is that I have more than 2 million lines in the table and power bi does not work

[@enzopinheironanni2042]

Great video! Just saved me a ton of work lol. I'm just wondering how I put an if statement before all this. Like just return the count IF PRODUCT = Quad.

[@dewetroets9211]

Thanks a million Mike. Great video. Is there a way to sum instead of count. I only see List.Sum (which won't work here as its a table I think). I want to calc a cum total per department in a table.(Sum one Col in a table). All I need is the total per department for every department repeated for every row.

[@bryangutierrez2399]

Cool stuff, i think it works only if the first parameter is a number, I’ll have to test it if all the columns are text. Probably need to add an Index column

[@AbaanaCharity]

I just figured a way to do this with out the big formula based on your count if by grouping Video. I started by duplicating the the Query that I wanted to count. Then transformed it to have the Group count. Then Merged in back with the original table to a new table which had the count in a separate Column. Seems to work well?

[@shafiulamin4128]

Can you tell how to implement counif(A:A, A1) in power query please?

[@GeertDelmulle]

Just use the Group By button. :-)

[@davebowman5392]

Thanks Mike

[@excelisfun]

You are welcome, Dave!!!!

[@analyticduck7904]

This is so close to what I am looking for! All your videos are great and easy to follow. What I am looking for is is DAX for a calculated column compatible to COUNTIFS(CATEGORY, ""&"",ENCOUNTER#,ENCOUNTER#,SALESPERSON,SALESPERSON). Can anyone provide insight?

[@johnborg6005]

Great Mike. :)

[@excelisfun]

Thanks for your consistent and amazing support, John Borg : )

[@nickdavidson5192]

How do you do this with 70k plus unique rows of data to cross reference?

[@semperdiscendum7439]

I use Dax but M confuses me!! But this was great! 👍👍 Keep such stuff coming Guru!! 👍👍

[@excelisfun]

Glad it helps, Shashikant!!!!

[@hwangso]

Hi Mike Amazing thank you for your great tip. Could you advise us how to do this in power bi? Thank you

[@diegosolisaraya5448]

Can this countif be done counting the whole column since the first table and not only the current and past items in the sorting?

[@ikaraburc]

Could anyone help me? While typing any formula, help screen is not coming?? why?

[@ramsree20]

Hi, Thanks for this share. But when I apply this on a data of 10K+rows, it takes a lot of time to load in to excel. Any help on this?

[@panupongdurongdacha8130]

Are you figure out yet?

[@technicalknowledge9128]

Sir I have one question Row basis condition=if(A2=A3, 0,1) row basis all down line how to apply this formula

[@sathyakk8719]

Hi! my data cannot be converted as 'number' as it is 10203040-10, 10203040-20 and so on. Therefore, I need to keep the data type as 'Text'. How can I use this formula in power query for Text Type?

[@hilaaroch1506]

Hi! How can I add another criteria (in addition to Product) to this formula? I tried using "And" but that didn't work..

[@aka3741]

I Tried it, after adding Custom Column I add two References to IT and OT. After it I couldnt see the list but functon on each row . And it says : "function (OT as any) as any"...it isnt possible make here list } not function? = Table.AddColumn(BufferedTable, "Count", each (OT) => Table.SelectRows(BufferedTable, (IT) => IT[Reference]&[Item]

[@debraindxb]

I was really hoping this would be a solution for something I'm trying to do just like that. I followed the steps exactly and when I got to this part: = Table.AddColumn(BufferedTable, "RunningCount", (OT) >= 1), PowerBI gave me, "Expression.Error: The name 'OT' wasn't recognized. Make sure it's spelled correctly." Can't continue :(

[@edge5817]

Hi Mike, How do you group by a column that is date data type and group it per month (e.g. counting the number of transactions per month)? thanks in advance :)

[@expertrocky316]

Sir, How to do this same thing in Power BI..

[@kiranghadi7088]

Please explain same function in VBA

[@clickbisi7016]

Funny how one of the easiest Excel formulas is one of the most difficult things to do in PQ

[@krn14242]

Thanks Mike. I was able to follow along and reproduce what you did, but, my Intelli-sense is not working in my version of Power Query. Is that only available in 365?

[@excelisfun]

Yes, in Office 365. Office 365 is THE only version going forward, you know that right?

[@ExactProBi]

Thanks, Mike for this amazing tip, but M Code operator IT and OT completely went over my head, could you please elaborate more? is there a detailed article or book I can refer to?

[@excelisfun]

Did you see the video link at the end of the video? For more about M Code #9 video in the MSPTDA class: kzbin.info/www/bejne/hISUcqmci551m6s for another video about variables like IT and OT: kzbin.info/www/bejne/aqqmlYKcq9J9jcU

[@priyanktyagi5725]

Same formula I want to run in power query Formula is- =1/Countif(A:A,A2) How to type this formula in power query Pls must reply...

[@josh_excel]

Here is may take on getting Running Count once the list is sorted using List.Generate: kzbin.info/www/bejne/kJiZdKOQZpd9j5Y

[@monttivani9254]

If I have one Row with 20 cells , and second row with 20 and I need merge the second row cells every tow cells only ,,, help me If you can please

[@excelisfun]

I do not undestand your question. I am sorry.

[@excelisfun]

For back and forth dialog try: mrexcel.com/forum

[@monttivani9254]

@@excelisfun I have about 170 rows (85 Bank , 85 filled) and 7 Columns , and i need to merge every tow rows (one blank with one filled) in easy way ...

[@alterchannel2501]

M code language is so difficult to remember

[@simfinso858]

Fearful for me 😨😱

[@excelisfun]

: )

[@rrrprogram8667]

Great one mike.... But how to handle this if we dont have "transaction no" column

[@excelisfun]

I do not think there is a way. As I mentioned, it makes no sense unless there is an order. If there is an order, but no marker like date, time or translation number, you could add an index, but if the external data does not have that marker and someone changes the order, it become meaningless.

[@georgypolskiy2849]

the name (OT) was not recognized. Full stop.

[@jitendrakumarsoni1993]

MTD,LM,L2LM,YTD,LYTD This format retail company mis report Plz create excel report this format

[@excelisfun]

I have no idea what you are asking...

[@excelisfun]

For back and forth dialog try: mrexcel.com/forum