Comparing SQL, Power Query, and DAX
In this exercise, you will use embedded SQL, Power Query, and DAX to analyze industry employment data. The goal is to walk through the same logic using different tools and compare how they work.
Step 1: Use Embedded SQL to Import Data
Click Get Data, then choose MySQL.
Enter the following connection details:
Server: dadata.bensresearch.com
Database: industry
Click Advanced Options to embed custom SQL queries. Paste and run each of the queries below one at a time:
SELECT TSE.DataYear, TSE.StateShort, SUM(HHI.Herf * ISE.Emp / TSE.Emp) AS WHHI FROM (SELECT StateShort, DataYear, SUM(ExpEmpValue) AS Emp FROM StateData WHERE IndustryCodeLength = 6 GROUP BY StateShort, DataYear) AS TSE JOIN (SELECT StateShort, DataYear, IndustryCode, ExpEmpValue AS Emp FROM StateData WHERE IndustryCodeLength = 6) AS ISE ON TSE.StateShort = ISE.StateShort AND TSE.DataYear = ISE.DataYear JOIN HHI ON ISE.DataYear = HHI.DataYear AND ISE.IndustryCode = HHI.IndustryCode GROUP BY TSE.DataYear, TSE.StateShort ORDER BY WHHI
SELECT * FROM HHI
SELECT * FROM StateData
SELECT StateShort, DataYear, SUM(ExpEmpValue) AS Emp FROM StateData WHERE IndustryCodeLength = 6 GROUP BY StateShort, DataYear
Rename your resulting tables as follows:
WHHI (for the weighted HHI results)
HHI
StateData
StateEmp (total employment by state)
Step 2: Import NAICS Labels
Click Get Data again.
Browse for the file NAICS.csv and load it.
Your result should look like this:
Step 3: Use Power Query to Merge Tables
Click Transform Data to open Power Query Editor.
Select the WHHI table.
In the top-right, choose Merge Queries as New.
In the merge window:
Select StateEmp as the second table.
Hold Ctrl and select DataYear and StateShort in both tables to match them (like a SQL ON clause).
Choose Inner Join.
Click OK.
A new column named StateEmp will appear. Click the small expand arrow and check only the Emp column.
Change the type of the DataYear column to text. This will make our next steps simpler.
Click OK, then Apply & Close.
Rename the resulting table to Merged.
Note: The merge screen in Power Query Editor should look familiar—it’s essentially a visual interface for performing a SQL-style join. When prompted, select the WHHI table as the first table, and then choose StateEmp as the second table. In both tables, hold Ctrl and click DataYear and StateShort to select them as the matching keys (just like using an ON clause in SQL). Choose Inner Join as the join type, then click OK. Don't forget that after the merge you’ll see a new column named StateEmp appear in the merged table. Click the small expand icon next to it, uncheck all fields except Emp, and then click OK (this functions like a SELECT statement in SQL). Finally, click Apply and Close in the top-left corner, and rename the new table to Merged.
Step 4: Create a DAX Measure
Right-click the Merged table and choose New Measure.
Use the DAX formula below to compute the national WHHI:
Measure = SUMX(Merged, Merged[Emp] * Merged[WHHI]) / SUM(Merged[Emp])
Step 5: Format and Visualize the Result
If you did not do this already, reopen Transform Data, select the Merged table, and change the type of the DataYear column to Text.
In Model View, select the new Measure, change its format to Decimal Number, and set it to 4 decimal places.
Create either a Table or a Bar Chart in the Report View.
Drag DataYear and your Measure into the visual.
Question
What is the national level of concentration (WHHI) in 2012?
Note
You may have noticed that using Power Query and DAX often involves a longer and more cumbersome process compared to simply writing a SQL query. It can also be significantly slower to execute. For these reasons, Power Query and DAX should not be seen as substitutes for SQL—rather, they are better suited as complementary tools, or a last resort when SQL alone cannot achieve the desired result.
Surveys of data professionals suggest that the average analyst spends roughly 80% of their time using SQL and only 20% using Power BI or similar tools. Writing efficient queries and designing a clean, well-structured database can reduce both processing time and computing costs, especially at scale.
As a bonus reflection, consider the following:
Does it make sense to weight market competitiveness (or concentration) by employment shares?
Can you think of an alternative weighting scheme that might be more appropriate?
How does industry concentration change over time? What do your results suggest?
Which tool or combination of tools would you choose if you were assigned this task in a real-world setting?
Would you follow the same steps again, or adjust your approach?