Awesome !!! Just use it to resolve a three (3) consecutive months of KPI performance, now ! Thank you, Oz. CONGRATULATIONS on your 2018 MVP award. You sure deserve it. You remember your VLOOKUP (Approximate Match) solution. It was an award winning !!!
Oyekunle SOPEJU WOAH! That's so amazing. Thanks for letting me know that the efforts have helped ... and it sounds like the lessons have been right on time for you. 👏👏👏👏👏
Another master class tutorial from the master. I learned lots of practical cases here and able to implement in my work. Thank you once again Oz for sharing the knowledge
Congrats on continuing to hold down your MVP spot! Dual indexes paired with conditionals are super useful for data that has repeats, but not consistent enough for a modulo. If something I need is always the same number of rows above a repeating unique string, I can line them up with a staggered index pair, merge, conditionals, and fills. I'm sure there's easier ways, but indexing has been my jam recently.
Congratulations on another MVP! Great job and great video! Thanks for sharing your knowledge! I use what I've learned from you in my everyday work life!
wow. Brilliant. thanks for sharing merge table with itself ... Congrats on getting another MVP award, you deserved that for sure and keep cleaning crap data with PQ.....
Thank you Oz. I had so much trouble with AND and IF togher, (in native Excel it's the other way around..). now it's clear. Why did not you make one formula with the other condition as well. For didactic reasons? anyway, you could use this formula in conditional formatting to support your analysis: (Andrea is in C5) =OR( AND(D5
Congrats on MVP. Another great video. You are the man, myth, and Excel Legend (not the chart kind). Glad we did not need any pitch forks, sticks, or lanterns (mine is still in the boat).
Thanks for the great video Oz. Any chance you can upload the file used in this video & if its not too much trouble same for future videos.That would a great help because then we can follow along.
NABIL!!!!!! Everyone who attended the MVP Summit maybe 3 years ago got one of those shirts. Sadly, I think they stopped giving out nice swag. We all got a cheap scarf on recent year. One size. All the same color. And cheap. LOL!
Here another solution, (similar to Bills in that it uses GroupKind.Local) but also shows the offending months. It uses a custom comparer. let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Unpivot = Table.UnpivotOtherColumns(Source, {"Name"}, "Month", "Performance"), ChangedType = Table.TransformColumnTypes(Unpivot,{{"Name", type text}, {"Performance", Int64.Type}}), Group = Table.Group(ChangedType,{"Name","Performance"} ,{{"Count", each Table.RowCount(_)},{"Table", each _}},GroupKind.Local ,(X,Y)=>if (X[Name]=Y[Name]) and X[Performance]
Thanks Oz for next PQ lesson :-))) I'm not sure if i understand the conditions correctly. Is it acceptable that we count Chris twice? Chris has 3 consecutive underperfomances so if we count first two, then third one is alone and this means that it doesn't meet the conditions. Am i wrong? Chris June July(1) Chris July(2) August
Bart, correct. Bill, in the real life situation my client agreed to do a service for their clients. In the agreement, if my client underperformed for 2 consecutive months, they couldn't bill those clients for those months. So, we had to identify that June, July and August could not be billed. One way to do that is to first identify and verify the periods: June July July August and then reduce those down to just the 3 months.
Thanks for explaining, Oz :-)) If i understand your needs correctly, the code below should do the job. let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Name", "Index"}, "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Underperfomance", each if [Value] 1)), #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Index", "Name"}, {{"Do not Pay for x months", each List.Sum([How many]), type number}}), #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows1",{"Index"}) in #"Removed Columns1" Second step (Add Index Column) i added to be safe if the next Name is the same. I've manually modified only the last part of step "Grouped Rows" - added the last argument of function Table.Group ( 0 as a 4th argument - it is equal to "GropuKind.Local") The remaining steps/parts are directly from UI. :-)))
@linelson - not yet, but soon. For this moment you can download the files i worked on, from the link below the videos. Excel automatically translates formulas from polish and my PQ solutions has the steps in english ( and the M-code is always in english). Most of the files contain some helper text in english and polish,
The short answer: yes. The more practical answer: That would be a situation where we'd need more details about the needs, restrictions and desired result. Two, 3 or 4 wouldn't be so bad. But, one question I'd ask a client on a real-life project, "would you ever need to go out to, say, 10 or 50?" The answer would dictate only part of the solution. Then we'd need to know if a graphic representation is needed or is there a calculation we're really trying to get at. In short, we'd need a more complex scenario to make the month a variable.
This will only work if there are two consecutive month of underperformance. What if there's more ? Can this solution be modified, so all of them are listed in one row ?
Answering that question is hard because it would really depend on your real-world needs. Even this solution with just 2 months was complicated. Below, Bill Sysz shows a great solution that COUNTS the number of months based on the rules. However, in the real-world solution we needed to show the offending months. So, let's say we want to flag periods of 6 weeks of under-performance; if you just need a count, then Bill's way would be much easier. If you do need to isolate those periods, my way would get tedious and messy. If this was real life, I'd ask my client what they need the data for and what questions they're trying to answer. Their answers might guide us to possibly a solution that neither Bill nor I came up with. You ask an interesting question because there are lots of long streaks that we can imagine. - 30-day streaks with no rain - 5-game streaks without allowing a score - 7-day streaks of going to bed at a reasonable time - Streaks of 10 deliveries that were on time And then it gets hard. What if we had 220 deliveries over the year and identify 3 streaks of 10 on-time deliveries? Next we really need to know if the 3 is good enough. Do we need to show the streaks? Do we then need to import details about each specific delivery? If we just need a visual, can we just use conditional formatting and be done? It's complicated.
Ammmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmzing, i was looking for that , thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you
Awesome !!!
Just use it to resolve a three (3) consecutive months of KPI performance, now !
Thank you, Oz.
CONGRATULATIONS on your 2018 MVP award.
You sure deserve it.
You remember your VLOOKUP (Approximate Match) solution. It was an award winning !!!
Oyekunle SOPEJU WOAH! That's so amazing. Thanks for letting me know that the efforts have helped ... and it sounds like the lessons have been right on time for you.
👏👏👏👏👏
can you please help me with 2 or more consecutive months
Another master class tutorial from the master. I learned lots of practical cases here and able to implement in my work. Thank you once again Oz for sharing the knowledge
WOW! Thank you so so much!
You're very welcome. 🔥🔥🔥
Absolutely amazing video. The two index columns technique is awesome. Keep up the good work Oz. Congratulations on becoming MVP again.
Khawar Ameer Malik Thank you! And you're so welcome! 😄
Congrats on continuing to hold down your MVP spot! Dual indexes paired with conditionals are super useful for data that has repeats, but not consistent enough for a modulo. If something I need is always the same number of rows above a repeating unique string, I can line them up with a staggered index pair, merge, conditionals, and fills. I'm sure there's easier ways, but indexing has been my jam recently.
Congratulations on another MVP! Great job and great video! Thanks for sharing your knowledge! I use what I've learned from you in my everyday work life!
Thank you!
It's good to know that this helps. I'm really humbled to know that you've been able to make use of the lessons here. 😊
Congratulations Oz! Well deserved award!
Awesome video...Poof the magic man srikes again...great job and Congrats, keep up the great work!!!
Thank you Thank you! 🔥🔥🔥🔥🔥
wow. Brilliant. thanks for sharing merge table with itself ...
Congrats on getting another MVP award, you deserved that for sure and keep cleaning crap data with PQ.....
Thank you Oz. I had so much trouble with AND and IF togher, (in native Excel it's the other way around..). now it's clear. Why did not you make one formula with the other condition as well. For didactic reasons? anyway, you could use this formula in conditional formatting to support your analysis: (Andrea is in C5) =OR( AND(D5
Congrats on MVP. Another great video. You are the man, myth, and Excel Legend (not the chart kind). Glad we did not need any pitch forks, sticks, or lanterns (mine is still in the boat).
🤣🤣🤣 yeah! Leave that stuff in the boat!
Primera vez que veo tu canal. Ya estoy suscrito. Muchas gracias.
First time I watch your channel. I'm already subscribed. Thank you very much.
Great example of when it can be useful to merge table with itself
Dziękuję! Yeah. Merging a table with itself is a pretty neat trick.
Thanks for the great video Oz. Any chance you can upload the file used in this video & if its not too much trouble same for future videos.That would a great help because then we can follow along.
KB Thanks for asking. Here's a link to the video. I'll see about making the workbooks available in future videos. 👍👍👍
datascopic.net/2months
Thanks Oz
Great to see Oz! Thanks for sharing :)
Yes yes! You're so welcome! 👍
Excellent work my friend
My friend, THANK YOU!!
Where did you get this nice polo shirt from? They should be selling it online since the summit went virtual 😂😂😂
NABIL!!!!!!
Everyone who attended the MVP Summit maybe 3 years ago got one of those shirts. Sadly, I think they stopped giving out nice swag. We all got a cheap scarf on recent year. One size. All the same color. And cheap. LOL!
I see that there is Year on Year financial comparisons in Power BI, have you done one in Power Query?
Here another solution, (similar to Bills in that it uses GroupKind.Local) but also shows the offending months. It uses a custom comparer.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Name"}, "Month", "Performance"),
ChangedType = Table.TransformColumnTypes(Unpivot,{{"Name", type text}, {"Performance", Int64.Type}}),
Group = Table.Group(ChangedType,{"Name","Performance"}
,{{"Count", each Table.RowCount(_)},{"Table", each _}},GroupKind.Local
,(X,Y)=>if (X[Name]=Y[Name]) and X[Performance]
yes...so damn sweet! Great example of using Power Query again
That's so much. Power Query is so much fun AND it tastes sweet. 🤣
Thanks for sharing OZ; I learnt a lot from you. Do you speak french?
Thank you!
No. I don't speak French. I got an F in high school French. 😫
Thanks Oz for next PQ lesson :-)))
I'm not sure if i understand the conditions correctly.
Is it acceptable that we count Chris twice?
Chris has 3 consecutive underperfomances so if we count first two,
then third one is alone and this means that it doesn't meet the conditions. Am i wrong?
Chris June July(1)
Chris July(2) August
Hi Bill, I think the solution is correct, Chris underperformes for two " time frames" , even if the are consecutive...
Bart, correct.
Bill, in the real life situation my client agreed to do a service for their clients. In the agreement, if my client underperformed for 2 consecutive months, they couldn't bill those clients for those months.
So, we had to identify that June, July and August could not be billed. One way to do that is to first identify and verify the periods:
June July
July August
and then reduce those down to just the 3 months.
Thanks for explaining, Oz :-))
If i understand your needs correctly, the code below should do the job.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Name", "Index"}, "Attribute", "Value"),
#"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Underperfomance", each if [Value] 1)),
#"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Index", "Name"}, {{"Do not Pay for x months", each List.Sum([How many]), type number}}),
#"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows1",{"Index"})
in
#"Removed Columns1"
Second step (Add Index Column) i added to be safe if the next Name is the same.
I've manually modified only the last part of step "Grouped Rows" - added the last argument of function Table.Group ( 0 as a 4th argument - it is equal to "GropuKind.Local")
The remaining steps/parts are directly from UI.
:-)))
Bill, do you make videos in english?
@linelson - not yet, but soon.
For this moment you can download the files i worked on, from the link below the videos. Excel automatically translates formulas from polish and my PQ solutions has the steps in english ( and the M-code is always in english). Most of the files contain some helper text in english and polish,
Your videos area GREAT! Thankfull
Hi, I have similar problem but with consecutive weeks performance. Would you be able to help? Thank you
Oz. YOU ROCK!
Thank you!
Impressive, congrats on Excel MVP status again... I can see why.
Thank you! 😊👍
Thank you for your video I've been looking for a solution
So damned sweet ... wasn't it? 😎
Too Good! thanks Oz Great Great Great and congrats!
Thank you, Thank you!!
Great help!!
Is it possible to make consecutive months dynamic too (instead of 2, 3 or 4)?
The short answer: yes.
The more practical answer:
That would be a situation where we'd need more details about the needs, restrictions and desired result. Two, 3 or 4 wouldn't be so bad. But, one question I'd ask a client on a real-life project, "would you ever need to go out to, say, 10 or 50?" The answer would dictate only part of the solution. Then we'd need to know if a graphic representation is needed or is there a calculation we're really trying to get at.
In short, we'd need a more complex scenario to make the month a variable.
Gracias.- Saludos
Congrats on MVP!!! :)))
Thank you!!
This will only work if there are two consecutive month of underperformance. What if there's more ? Can this solution be modified, so all of them are listed in one row ?
Answering that question is hard because it would really depend on your real-world needs. Even this solution with just 2 months was complicated.
Below, Bill Sysz shows a great solution that COUNTS the number of months based on the rules. However, in the real-world solution we needed to show the offending months.
So, let's say we want to flag periods of 6 weeks of under-performance; if you just need a count, then Bill's way would be much easier. If you do need to isolate those periods, my way would get tedious and messy. If this was real life, I'd ask my client what they need the data for and what questions they're trying to answer. Their answers might guide us to possibly a solution that neither Bill nor I came up with.
You ask an interesting question because there are lots of long streaks that we can imagine.
- 30-day streaks with no rain
- 5-game streaks without allowing a score
- 7-day streaks of going to bed at a reasonable time
- Streaks of 10 deliveries that were on time
And then it gets hard. What if we had 220 deliveries over the year and identify 3 streaks of 10 on-time deliveries?
Next we really need to know if the 3 is good enough. Do we need to show the streaks? Do we then need to import details about each specific delivery? If we just need a visual, can we just use conditional formatting and be done?
It's complicated.
Nice solution
Thank you!
Congrats
Thank you!
Ammmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmzing, i was looking for that , thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you thanks you