In some cases, Power Query’s functionality may not be as robust as Excel’s, especially when working with median and quartile calculations. One way to overcome this is by bouncing data through Excel, allowing you to take advantage of Excel’s powerful functions. By letting Power Query handle the data retrieval and clean up, Excel can perform the quartile calculations, and you will be able to efficiently calculate the lower quartile, upper quartile, and median values of a data set and add it back into the data set. This way you can show something like shares and the quartile statistics in the same data/ charts.
Table of contents
YouTube: Median and Quartiles in Power Query- bounce through Excel
Setting Up Your Data for Quartile Calculations
To start, create a list of companies or data points that you want to analyze (A7 to C15 below). From here, get Power Query to pull in the selected data and filter onto the correct data. You’ll need to set up a table in Excel (H2 to I5 below) that can automatically adjust to the data pulled in by Power Query (E7 to F12 below). In this table, ensure you label the rows for upper quartile, median, and lower quartile, as seen in the image below. It is helpful to give these columns the same names as the ones used in your Power Query selection to keep everything consistent and easy to follow.
Using Excel’s QUARTILE Function.
Once your table is set up, you can leverage Excel’s QUARTILE function to perform the calculations. For the upper quartile, use the QUARTILE function with a value of 3 in the Quart option, for the median use 2, and for the lower quartile use 1. This ensures that as you change the data, Excel will automatically recalculate the quartiles. The images below illustrates how the QUARTILE function is applied to calculate these values.
Incorporating the Table into Power Query.
Next, you’ll bring this Excel table back into Power Query by clicking on Data, followed by From Table or Range. This table, now labeled as “Calcs” in Power Query, will be appended to the company selection list. To do this, navigate to Home and select Append Queries as New. In the dialog box, choose to append the company selection query to the “Calc” table. Since the column names in Excel match the ones in Power Query, this process is streamlined, as shown in the image below.
You’ll then rename the query to something like “Final” to distinguish it. Once the query is appended, sort the results in ascending order to ensure the upper quartile, median, and lower quartile values appear in the correct place in your dataset. Finally, load the query back into Excel by selecting Close & Load To…, and you could choose to load it into a new worksheet and then copy into your main worksheet.
Validating and Refreshing for Accuracy.
When bouncing data through Excel, it’s important to refresh the data twice to ensure the quartile calculations are accurate. The first refresh updates Power Query, pulling the selected data, but the quartile values will not fully update until you perform the second refresh. Start by selecting Data, then Refresh All. Once Power Query has updated, check if the quartile values (upper, median, lower) are correctly aligned. To confirm accuracy, a second refresh is necessary. Refresh once more by selecting Data and then Refresh All to update the quartile calculations in Excel.
To ensure your results are reliable, we suggest you build in some checks. In the example below in rows E3-E5, we used the formula =VLOOKUP(Calcs[@Company],Final,2,FALSE) to search for the upper quartile value for each company from the final data in Power Query and compare it with Excel’s quartile calculation. In rows F3-F5 we used the formula =IF(E3=Calcs[@Amount],”OK”,”Refresh Again”) to verify whether the values match or if another refresh is required. These additional step helps prevent errors and ensures that Power Query and Excel’s data outputs are in sync.
Tips and Troubleshooting.
- Ensure Consistent Table and Column Names: When working between Power Query and Excel, it’s important that the table and column names match exactly to avoid errors during the append process. For clarity, casing is important so Company is different from company.
- Check for Double Refreshes: Always remember that bouncing through Excel often requires two (or more) refreshes: one for each time Power Query needs to pull data in and load it back.
- Use VLOOKUP for Validation: Implement checks using VLOOKUP or other comparison functions to verify that your Power Query and Excel outputs are aligned and accurate.
- Beware of Regional Settings: If your system uses different regional date or number formats, it might affect how Excel interprets the data. Ensure consistency in your date and number formatting.