July is “Power Query Month”, where the theme is focused less on visualization and more on data preparation. Every Power BI challenge requires Power Query to get and transform data, but now we’re going deeper in July!
Starting off the month, you’ll use Power Query to perform preliminary cleanup and filtering of data exported from Morningstar financials. Rather than focus on all the key financial ratios, you’ll filter for a subset of them, reshape the data as needed, and present some basic data visualization. We’re not trying to wow anyone with our matrix and line chart this week.
- Export Morningstar “Key Ratio” data for Microsoft to CSV from https://financials.morningstar.com/ratios/r.html?t=MSFT
- Click the Export button (no need to scrape or use the Web connector–although kudos to you if you want to try that route)
- The file that downloads should be called “MSFT Key Ratios.csv”
- Import the CSV into Power BI Desktop and select Transform Data to tidy the dataset
- Filter the data or remove rows so that only the what appears in Morningstar’s “Financial Health” and “Efficiency” tab tables remain (Balance Sheet Items, Liquidity, Efficiency)
- Add an Index column in the query
- Add a conditional column named “Category” that uses your Index values to label each financial measure with the appropriate category, e.g. “Balance Sheet Items”, “Liquidity/Financial Health”, “Efficiency” (or find a more eloquent way to do this)
- Promote column headers
- Remove any unnecessary/extra records so that only legitimate financial data appears on rows
- Unpivot so that time period are on rows instead of columns
- Rename any columns as needed so that headers make sense
- Set appropriate data types
- Load the clean data to your model
- Add a Matrix visual to the report canvas and recreate the Morningstar Financial Health tab as closely as possible (mind the sort order!)
- Add a Line Chart visual with time period on the axis and a value that only appears when you select a row on the matrix (requires DAX)
- Add a dynamic title to the Line Chart that displays the currently selected measure when you select it from the matrix (requires DAX)
This week’s dataset consists of a subset of Microsoft financial ratios from Morningstar: https://financials.morningstar.com/ratios/r.html?t=MSFT
After you finish your workout, share on Twitter using the hashtags #WOW2021 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd and @dataveld. 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.
5 thoughts on “2021 Week 27 | Power BI: Data Transformation with Power Query”
Hey team, creating the matrix was ok and the line graph. However, can you help point me to how to create the 3 rectangles to show/filter the appropriate categories?
Hi, Ben: The rectangles are a slicer visual. The slicer is populated with the Category values described in the requirements.
Is the video solution available again please. Thanks.
Dataset isnt available, it shows “The report is no longer supported”
Hi Bryan – apologies it looks like that source data isn’t available anymore. We welcome you to try the next challenge! Thanks for letting us know 🙂