This is still ongoing… any clue by the pic.
@FPastor The performance can be incresed by 11x but you need to create a Date Column in the Excel file itself.
Tested with 30,000 rows.
Federico - Rows To Keep Optimized.pbix (20.5 KB)
Data Need to sorted (1).xlsx (1.1 MB)
3 Likes
Nice! I think the solution on this and the subsequent optimization would make for an excellent video. Just sayin’…
- Brian
3 Likes
@BrianJ and @AntrikshSharma - let me do my testing and back to you asap.
THANK YOU BOTH FOR YOUR GREAT HELP
Federico
2 Likes
@BrianJ solution is very fast but the solution failed when we have more records on the same day.
See attached.
Data Need to sorted (1) (2).xlsx (10.1 KB)
You can see in the file when I applied the same steps in the complete data set is giving wrong answers.
Thanks
Federico
1 Like
@FPastor ,
Can you please provide me your larger test dataset? I think I know exactly what I need to do to fix this, but want to test it before saying for certain.
Thanks.
- Brian
2 Likes
@FPastor That’s expected, the code has some hard coded columns such as in the expanded column step, just fix them.
1 Like
@BrianJ I am afraid I cannot pass the original file as is a real work file.
Thanks for your help.
Federico
Still awaiting results. Back to you asap.
Thanks
Federico is Legend Status
2 Likes
@FPastor ,
Sorry for the delay – been consumed with prep for the upcoming Analytics Summit. Would you be able to provide me with a slightly larger mock dataset with the multiple entries that caused problems with my initial solution?
I’m eager to get you a revised solution that runs at acceptable speed. Thanks!
– Brian
1 Like
Hi Brian,
I think we’re going to put this down to rest. The main problem is, in my view, the solution is complex and not easy to apply in a real work project when the user/developer skills are not up to this level yet. Put it this way, the solution and code scare the hell out of me and if it scares the hell out of me with a bit of knowledge, think about somebody with less knowledge.
Nonetheless very grateful for your help and time spent on this matter. Perhaps if you guys decide to put a video on it, I can check it out again and revert back.
Federico
PS. The M written in that code is insanely good!!!
Hi Ant,
As I mentioned to Brian, I think we’re going to put this down to rest. The main problem is, in my view, the solution is complex and not easy to apply in a real work project when the user/developer skills are not up to this level yet. Put it this way, the solution and code scare the hell out of me and if it scares the hell out of me with a bit of knowledge, think about somebody with less knowledge.
Nonetheless very grateful for your help and time spent on this matter. Perhaps if you guys decide to put a video on it, I can check it out again and revert back.
Federico
PS. The M written in that code is insanely good!!!
Hi… I have been following this post keenly since it started.
Really thankful to @BrianJ, @AntrikshSharma and @DavieJoe for their contributions.
Till now, I was not aware that two different sets of code operating on the same dataset, while yielding the same results, could have significant differences in execution times.
@FPastor, thanks for this learning opportunity.
As @BrianJ suggested, we are all eager to see video on a working solution with acceptable performance.
1 Like
Oh, the differences can be extreme. There was a thread a while back that I solved with what I thought was pretty good code. It gave the right answer but the original poster said it took about 10 minutes to run on his larger dataset. @Melissa rewrote the code and it ran in just a few seconds.
- Brian
P.S. Ugh. I found the thread, and it was worse than I thought. My code took nearly an hour (!) to run. Melissa’s took only a few seconds on the exact same dataset. Thankfully, I have improved since then…
3 Likes
