This weekend, Fantasy Football teams will be doing their draft. If you are using the ESPN website to manage your draft, the Draft Recap Report could use some formatting.
Download our free Excel template from: Transform Your ESPN Fantasy Football Draft Recap Report
Watch the video to see how to copy your Draft Recap from ESPN, paste it into Excel, and then use Power Query to clean it up and format it.
Thanks to Rob Collie at P3 Adaptive - Leading Consultants on Power BI & the MS Data Platform
Thanks to Suat Ozgur from MrExcel.com
Download our free Excel template from: Transform Your ESPN Fantasy Football Draft Recap Report
Watch the video to see how to copy your Draft Recap from ESPN, paste it into Excel, and then use Power Query to clean it up and format it.
Thanks to Rob Collie at P3 Adaptive - Leading Consultants on Power BI & the MS Data Platform
Thanks to Suat Ozgur from MrExcel.com
Transcript of the video:
Well, this weekend across the country people will be doing their fantasy football draft.
And if you're using ESPN to manage your league, the draft recap that they have there is ugly.
And we have free download transform their draft recap report into this beautiful report.
Hey, I am Bill Jelen from MrExcel com.
Today's question: “Hey Mr ESPN-draft-recap-designer, is that really the best you can do?
This is an ugly draft recap.
It doesn't look like the draft recaps that I've seen in the past with other leagues”.
Now, my friend Rob Collie, author of this best selling book and the principle of P3 Adaptive: the best Power BI consultants.
If you're trying to get up to speed on Power BI, check out Rob and his company.
They'll get you set up. Rob is a huge fantasy football fan.
Rob and I were talking about the the ESPN recap and we said we can do a better job.
Rob built the first prototype of this.
It looked beautiful and then my web guy Smozgur – a genius with M code, he made it work for any number of teams and any team names and so on.
Alright, so here's what you're going to do. Down in the YouTube description below.
You'll download this workbook. It has two sheets: Input and Output.
It is BetterESPNDraftRecap.xlsx, Here on sheet one is something called Table1.
That name has to stay the same. We need those three columns.
Go in to ESPN Fantasy sports. Go to your league.
Find your league and go to the Draft Recap. Now they have two views: By Round and By Team.
Make sure to choose By Round.
Very carefully starting selecting here, just before “Round 1” and drag down.
It tends to go too fast - make sure you don't go too far. Select all of the data down to the last round.
Control+C to copy. Then come back to Excel right here in cell A2.
That one cell right there and Ctrl+V. And your data will become part of this table.
Now the amazing thing is we're using some tools called Power Query.
If you've never seen Power Query before, they are here under Get and Transform Data.
It takes ugly data like this and gives you tools to turn it into beautiful data like this.
Right now what you're going to do is on the Data tab you're going to click Refresh All.
And that will update this grid for your draft.
The person who picks first in round one picks last in round two, so that means that the data on the Input tab, right here , Orange Sherbert picked first.
But then in the next round the Deloitte Terminators picked first.
So the Power Query that we are using here is very careful to organize round one left to right, but then round two right-to-left.
Every odd-numbered round goes left to right. Every even numbered round goes right to left.
Now the colors. That's not power query.
That's just straight old-fashioned Home , Conditional Formatting. Manage rules.
Rob set this up so that way each position has a different color.
You can just see it a glance know that the first kicker, Justin Tucker, wasn't picked until round nine.
A little bit of formatting that you might want to do and the power query refresh is going to undo this for you.
If you want to get everything to fit on one page: maybe a little bit of Home, Format, Column Width, maybe a nice little column width of 16 or so. That gets all of my teams on a landscape page.
And then watch out with Wrap Text turned on. You're not seeing all of the text.
So, that's Home, Format Autofit Row Height like that, right?
And then we have a nice reports. Great recap. OK. Thanks to Rob for this idea.
Thanks to Smozgur for writing the M code.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
OK, for those of you who are Power Query fans and want to geek out here and look at this M code.
Right-click out here. Edit.
And then come back into View, and the Advanced Editor. Just some gorgeous-looking M code here.
Not built using the interface, just built using M.
My good friend Suat Ozgur who manages the MrExcel website as our database administrator. He just writes beautiful looking M code.
So check that out.
You know right here is where it's checking if the round is odd then ordered descending. Otherwise order descending.
Very, very concise, awesome awesome code. Thanks to Suat for that.
And if you're using ESPN to manage your league, the draft recap that they have there is ugly.
And we have free download transform their draft recap report into this beautiful report.
Hey, I am Bill Jelen from MrExcel com.
Today's question: “Hey Mr ESPN-draft-recap-designer, is that really the best you can do?
This is an ugly draft recap.
It doesn't look like the draft recaps that I've seen in the past with other leagues”.
Now, my friend Rob Collie, author of this best selling book and the principle of P3 Adaptive: the best Power BI consultants.
If you're trying to get up to speed on Power BI, check out Rob and his company.
They'll get you set up. Rob is a huge fantasy football fan.
Rob and I were talking about the the ESPN recap and we said we can do a better job.
Rob built the first prototype of this.
It looked beautiful and then my web guy Smozgur – a genius with M code, he made it work for any number of teams and any team names and so on.
Alright, so here's what you're going to do. Down in the YouTube description below.
You'll download this workbook. It has two sheets: Input and Output.
It is BetterESPNDraftRecap.xlsx, Here on sheet one is something called Table1.
That name has to stay the same. We need those three columns.
Go in to ESPN Fantasy sports. Go to your league.
Find your league and go to the Draft Recap. Now they have two views: By Round and By Team.
Make sure to choose By Round.
Very carefully starting selecting here, just before “Round 1” and drag down.
It tends to go too fast - make sure you don't go too far. Select all of the data down to the last round.
Control+C to copy. Then come back to Excel right here in cell A2.
That one cell right there and Ctrl+V. And your data will become part of this table.
Now the amazing thing is we're using some tools called Power Query.
If you've never seen Power Query before, they are here under Get and Transform Data.
It takes ugly data like this and gives you tools to turn it into beautiful data like this.
Right now what you're going to do is on the Data tab you're going to click Refresh All.
And that will update this grid for your draft.
The person who picks first in round one picks last in round two, so that means that the data on the Input tab, right here , Orange Sherbert picked first.
But then in the next round the Deloitte Terminators picked first.
So the Power Query that we are using here is very careful to organize round one left to right, but then round two right-to-left.
Every odd-numbered round goes left to right. Every even numbered round goes right to left.
Now the colors. That's not power query.
That's just straight old-fashioned Home , Conditional Formatting. Manage rules.
Rob set this up so that way each position has a different color.
You can just see it a glance know that the first kicker, Justin Tucker, wasn't picked until round nine.
A little bit of formatting that you might want to do and the power query refresh is going to undo this for you.
If you want to get everything to fit on one page: maybe a little bit of Home, Format, Column Width, maybe a nice little column width of 16 or so. That gets all of my teams on a landscape page.
And then watch out with Wrap Text turned on. You're not seeing all of the text.
So, that's Home, Format Autofit Row Height like that, right?
And then we have a nice reports. Great recap. OK. Thanks to Rob for this idea.
Thanks to Smozgur for writing the M code.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
OK, for those of you who are Power Query fans and want to geek out here and look at this M code.
Right-click out here. Edit.
And then come back into View, and the Advanced Editor. Just some gorgeous-looking M code here.
Not built using the interface, just built using M.
My good friend Suat Ozgur who manages the MrExcel website as our database administrator. He just writes beautiful looking M code.
So check that out.
You know right here is where it's checking if the round is odd then ordered descending. Otherwise order descending.
Very, very concise, awesome awesome code. Thanks to Suat for that.
