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!
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.
Glad you like my videos. Here is a video that goes over M Code basics: th-cam.com/video/NS1AvfV9BeI/w-d-xo.html Here is the full playlist: th-cam.com/play/PLrRPvpgDmw0kuIuvn2rCQI14AthSZ43ca.html
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 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.
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!!
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: th-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html
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.
My free classes here at TH-cam set up all the theory. Here is Video #9 in the MSPTDA class that has all the theory: th-cam.com/video/NS1AvfV9BeI/w-d-xo.html
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....
@@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!!!
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
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.
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.
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?
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.
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.
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.
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"
Also, the next video, Power Query Running Count with Grouping & Add Index - Excel Magic Trick 1589, th-cam.com/video/camNMc1zaBc/w-d-xo.html, shows your trick : )
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?
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
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
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?
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 :)
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?
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 :(
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?
Did you see the video link at the end of the video? For more about M Code #9 video in the MSPTDA class: th-cam.com/video/NS1AvfV9BeI/w-d-xo.html for another video about variables like IT and OT: th-cam.com/video/4yCdOfvmJWc/w-d-xo.html
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]
@@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 ...
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.
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
![Power Query Classic COUNTIFS | SUMIFS | AVERAGEIFS | MAXIFS | MINIFS Calculations [Simply Amazing]](http://i.ytimg.com/vi/5rjNmAZLeWo/mqdefault.jpg)
![Power Query Classic COUNTIFS | SUMIFS | AVERAGEIFS | MAXIFS | MINIFS Calculations [Simply Amazing]](/img/tr.png)
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!
Mike, what I would do without you? Every time I need excel/power query idea - I find you! 🙏
Glad to help, FRANKWHITE1996 : ) : )
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.
Glad you like my videos. Here is a video that goes over M Code basics: th-cam.com/video/NS1AvfV9BeI/w-d-xo.html
Here is the full playlist: th-cam.com/play/PLrRPvpgDmw0kuIuvn2rCQI14AthSZ43ca.html
Thank you Mike... Awesome
I had to watch it 3 times before I'm able to do it
You are welcome!!!! Practice many times and you can get good. That is how I do it too : )
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
Much better way to do it! Thanks for that awesome tip from you and Bill Szysz : ) That will be tomorrow's video!
You don't use bufferredTable?
@@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.
i love you, Vida!
You Sir, are my new favourite person! Half a day searching for an answer that I followed in less than 30 minutes.
Glad to help : )
Mike. This is exactly what I needed before continuing with the 365 MECS 04. So grateful!
The legend of excel ❤❤
This method is slow on big data. But the multiple step process can avoid the slowness: th-cam.com/video/camNMc1zaBc/w-d-xo.html
You taught me a lot about power query than any one book 📕 keep up the good work Sir
You rock !!
Glad to help!
Power Query, the number 1 package from Microsoft, Thanks You
Power Query IS pretty amazing!!!!! Thanks for stopping by, Ogwal!!!
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!!
You are welcome, Wayne! I agree that sometimes the Worksheet formulas are easier : )
Super understandable explanation!🙏🙏🙏
Brilliant. I was just thinking where do I get started with the syntax and you mentioned them right at the end. Thanks Mike.
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: th-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html
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 : )
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.
My free classes here at TH-cam set up all the theory. Here is Video #9 in the MSPTDA class that has all the theory: th-cam.com/video/NS1AvfV9BeI/w-d-xo.html
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. 😊
Hi! This is exactly what I need right now. Thanks a lot for this amazing video!
Somehow I missed this video when it came out. Thanks for for the great explanation Mike. This solution will be useful for sure.
Glad you found it now! But with all single cell Power Query solutions, be careful on big data sets, cuz it might be slow : (
Brilliant, Mike! As always. :)
Always glad to help : )
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.
Glad the video helps!
Hi, extremely helpful video, thank you!
power query magic from excelisfun.....Excel-e-lent! thanks mike.
Thanks, Doug!!!!! Glad it is EXCELlent for you : ) : ) : )
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....
Beautiful video. Thanks Mike for the awesomeness.
You are welcome, Syed : ) : )
Fantastic Video
Ive beennlooking for this all my life!!!! hahaha. thank you very mucho!!!
WHAT ???!!! ..... WOW ....i'll go directly to your MSPTDA ....thanks Mike
MSPTDA is good : )
magic 🙌
Thank you very much Sir
Thank you for the variety of the magnificent Excel fun Mike 🤗
You are welcome for the variety and magnificent fun, Katerina!!!!
Amazing, thank you!
YOu are really great, what if instead of counting rows i would need to sum them? thanks
Very Useful. Thx
Glad it was helpful!
That was exactly my problem!
Thx
thank you. It is so helpful
Mike, what an amazing video!!! Thanks to sharing this.
You are welcome, Karoline!!!
Thank you Mike!
Great One Mike. Beautiful Staff. :) :)
Thanks, John Borg : ) : )
Fantastic!
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.
Nice video Mike!
Glad it is nice for you, Chris : )
Mind blown. Thank you!
You are welcome, Most Awesome Excel Sister!!!!! The next video is an even better way to do this : )
@@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!!!
Impressive Video
Thank you
Thank you so much!!!
Amazing! PQ is so much different from Excel... And getting easier and easier with intelli sense ;)
Yes, the intelli sense does help : )
Very good, i am searching this thing
This is brilliant. Could have used this a long time ago
Glad it is here now for you, Donovan!!!! Thanks for your support : )
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
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.
Works great for small sample but takes so much time for big samples ( 160k rows)
Yes, that is true : (
ExcelIsFun do you know of there is any alternative to do it on big samples ?
@@gregoirebertrand5787Hi bro, did you get the alternative ways?
Thanks for the video..
You are welcome, aditya!!!!
Imbelivable mike :) no comment briliant
Amazing..
Hi Mike
Amazing thank you for your great tip.
Could you advise us how to do this in power bi?
Thank you
Beautiful logic in M language :-)) Thanks.
Thanks, Teammate, Bill "Mr Power Query" Szysz : ) : ) !!!!
Great Mike. :)
Thanks for your consistent and amazing support, John Borg : )
Can this countif be done counting the whole column since the first table and not only the current and past items in the sorting?
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.
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?
Incrível!!! Muito obrigada me ajudou muito!
Ola, boa tarde!
Consegue me explicar, estou precisando muito fazer essa formula
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.
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.
Thanks for being such a great Teammate!!!
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.
@@GeertDelmulle Thanks, Teammate Geert! Via Bill Szysz, there may be an even better way. Maybe a new video tomorrow or the next day : )
What?!? Can’t wait to see that one!...
Thanks Mike
You are welcome, Dave!!!!
Can you tell how to implement counif(A:A, A1) in power query please?
Just use the Group By button. :-)
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"
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"
BTW, Vida and Bill Szysz also suggested this : )
Also, the next video, Power Query Running Count with Grouping & Add Index - Excel Magic Trick 1589, th-cam.com/video/camNMc1zaBc/w-d-xo.html, shows your trick : )
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?
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?
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
How do you do this with 70k plus unique rows of data to cross reference?
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?
Are you figure out yet?
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
Please explain same function in VBA
I use Dax but M confuses me!! But this was great! 👍👍 Keep such stuff coming Guru!! 👍👍
Glad it helps, Shashikant!!!!
Hi! How can I add another criteria (in addition to Product) to this formula? I tried using "And" but that didn't work..
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?
Yes, in Office 365. Office 365 is THE only version going forward, you know that right?
Sir,
How to do this same thing in Power BI..
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 :)
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?
Could anyone help me?
While typing any formula, help screen is not coming?? why?
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 :(
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?
Did you see the video link at the end of the video? For more about M Code #9 video in the MSPTDA class: th-cam.com/video/NS1AvfV9BeI/w-d-xo.html for another video about variables like IT and OT: th-cam.com/video/4yCdOfvmJWc/w-d-xo.html
Sir I have one question Row basis condition=if(A2=A3, 0,1) row basis all down line how to apply this formula
Funny how one of the easiest Excel formulas is one of the most difficult things to do in PQ
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]
Here is may take on getting Running Count once the list is sorted using List.Generate:
th-cam.com/video/Zg6CpZ12JY4/w-d-xo.html
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
I do not undestand your question. I am sorry.
For back and forth dialog try: mrexcel.com/forum
@@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 ...
Great one mike.... But how to handle this if we dont have "transaction no" column
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.
Fearful for me 😨😱
: )
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...
M code language is so difficult to remember
the name (OT) was not recognized. Full stop.
MTD,LM,L2LM,YTD,LYTD
This format retail company mis report
Plz create excel report this format
I have no idea what you are asking...
For back and forth dialog try: mrexcel.com/forum
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