OK, here we go!
Problem of the Week #9 is now live! The YouTube video introducing this week’s problem can be found here.
For background on this initiative, check out this post .
A couple of curveballs right off the bat. First, this would typically be the week for a DAX problem, and in the expert rotation it would be led by @haroonali1000. However, due to scheduling issues, he and I have swapped for the month, so you will get the DAX challenge with Haroon in a couple weeks.
Second, while almost all of our problems are drawn from real-world business examples, this one is a combination of my love of: movies (in particular the Academy Awards); the way Power Query can simplify otherwise complex problems; and the Queryon Timeline custom visual. However, I think as you start working on this you will quickly see direct business applications for the techniques it requires.
To start with, you are given two tables: @Melissa’s awesome Extended Date Table and a second table called Actresses, that you are NOT to use in solving the primary problem – that table is intended only to provide information that you will use for the visualization portion of this problem. In addition, you will be given a large CSV file, that will form the basis of your fact table.
The task for this challenge is to take the CSV data provided (a dataset from Kaggle.com, listing every Oscar-winner in the history of the Academy Awards). Your focus for this analysis will be the Best Actress award (also known as Best Actress in a Leading Role – an important tidbit to keep in mind, hint, hint…). In the history of the Oscars, 14 women have one more than one Best Actress award.
You are tasked with identifying these 14 women in the dataset and the year that each of them won their second Best Actress Oscar . You are to then take this information and visualize the results in a horizontal timeline, using the Queryon Timeline custom visual provided in the attached PBIX file.
How to Approach This Problem
Given that this is a Power Query only challenge, the two categories of tools available to you are the Power Query UI and the M language. The challenge can be successfully completed using only the UI. However, I think the most direct routes to a solution will involve both the UI and a small amount of custom M.
Can You Crack This?
In terms of resources available to help you, I did a short intro video to this problem on YouTube. Also, following Data Challenge #7, where I first used the Queryon Timeline, I did a detailed YouTube video on it, accompanied by an equally detailed blog entry.
Posting Your Solution
We ask that if you post anything directly related to a solution to please use the “Hide Details” or “Blur Spoiler” options within your post. This will prevent people who have not yet completed their solution from seeing things they might not want to see yet and could influence their solution.
To use these options, just select the text or image you want to hide, click on the gear icon on the top right at the top of your post and select either Hide Details or Blur Spoiler.
Note that this is a Power Query only challenge, so no DAX even if that is what you would choose to use outside of this challenge.
EDNA Problem Solver Stickers
Everyone who successfully completes a Problem of the Week will receive one of these cool Enterprise DNA vinyl laptop stickers:
To receive your sticker, when you submit your solution, just send a copy of your physical mailing address to firstname.lastname@example.org. (Note: my apologies – I have been so busy working with Sam, the other experts and the EDNA team rolling out new courses, YouTube content and community events and initiatives that I’ve fallen behind on the sticker mailing. If I owe you a sticker, I will get it to you – sorry for the delay).
Deadline and Eligibility
If you want to submit your entry before the solution becomes public, please do so before 10am ET Wednesday April 14, 2021 . Reminder that Problem of the Week is open to everyone – members and non-members. Members should just post your solution directly to this thread in the forum. Non-members can send your solution to me at the email address above or at email@example.com and I will post it to the forum thread for you.
We hope you find this initiative fun, challenging and helpful in continuing to build your Power BI skills. All of the information you will need is contained in the files within this post. If you have any questions or comments, just message me at @BrianJ in this forum thread or at the email address above.
Good luck, and enjoy!!!
P.S. Your feedback is always welcome, and if you have a real-world problem that you think might make a good future Problem of the Week, please email it to us at firstname.lastname@example.org. Thanks!
P.P.S. Shout out and huge thanks to the awesome team at Queryon. Right before I finalized this problem, Microsoft changed the requirements for certification for custom visuals, and this change broke the URL image linking feature in custom visuals using external image links, including Queryon Timeline. To solve this problem, the Queryon team compiled and sent me a special version of the program that re-enables this image linking feature. I’ve embedded that version in the PBIX file attached.
Here are the files you will need: