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.
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.