Introduction
This week we are going to have some fun with Power Query to obtain data from a website that spans multiple pages and then visualize the data in a treemap.
Treemaps are meant to display hierarchical data as a set of nested rectangles. They aren’t a great replacement for bar charts when minor differences are important. They are good at showing part-to-whole relationships in a large amount of hierarchical data.
We are using the built-in treemap visual in Power BI. As you work with the treemap visual, note anything you think is missing or could be improved. There is a hashtag #PBICoreVisuals being used on Twitter, LinkedIn, the Power BI Community forums, and other social media platforms to discuss the future of visualizations in Power BI. You can share your opinions about the tree map and other core visuals in Power BI using this hashtag. You can watch this video to learn about the current planned roadmap for visual improvements and see the responses from the Power BI team on social media. There is currently a very active discussion around the core visuals (with responses from the responsible program manager at Microsoft), and we encourage you to participate.
Requirements
1. Retrieve the data from the Top S&P 500 Companies Ranking by Market Cap table at https://disfold.com/stock-index/sp-500/companies/. Note: The data is paginated and you must retrieve the data from the subsequent pages, not just the first page.
2. Retrieve the data from the Components of the S&P 500 table at https://www.slickcharts.com/sp500.
3. Merge the data from the two sources together into a single table based upon the Stock Symbol values. Note: Before filtering to the top 200 companies (in the next step), the last 27 or so values may not match up. That is just a discrepancy between the data sources, which happens in real life.
4. Filter the data to include only the top 200 companies based upon the ranking (the [#] column) from Slickcharts.
5. Create a treemap that displays each company by stock symbol grouped by sector with rectangle sizes based upon market cap.
6. Add the Industry, Company and Weight fields in the tooltip.
5. Use a different color from the viridis color palette for each sector. Ensure that the data labels and category text have good contrast, using dark text on light colors and light text on dark colors.
Dataset
There are two data sources this week, both public web pages.
- The Components of the S&P 500 table at https://www.slickcharts.com/sp500.
- The Top S&P 500 Companies Ranking by Market Cap table at https://disfold.com/stock-index/sp-500/companies
Share
After you finish your workout, share on Twitter using the hashtags #WOW2023 and #PowerBI, and tag @MMarie, @shan_gsd, @KerryKolosko. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.
Solution
Solution File available for download via Data Stories Gallery.
Analysis of average weekly household expenses by regions for the period 2007-2009 allows you to identify noticeable trends and differences in consumer behavior. It is possible that housing costs were higher in the central regions, while heating costs were higher in the northern regions. This data can be useful for developing economic strategies and social support.
Hi Meagan, The way you pulled the data was really interesting, but I am having trouble finding resources online that explains the functions you were using. How would I find more information on how you pulled in this data and created the functions to pull the paginated dataset?
Apologies for the slow response. Is there a particular thing you are interested in? I initially used the Web connector UI to get a single page, then I edited that query into a function. This looks somewhat similar to my approach: https://www.phdata.io/blog/using-m-to-create-a-custom-function-for-web-scraping/. For more info on functions in general, this might be helpful: https://learn.microsoft.com/en-us/power-query/custom-function