Excel’s Power Query Editor and Power BI

Power Query is found within both Excel and Power BI, helping users to transform and clean up their data before they analyze it. With Power Query, you can easily connect to data sources like Excel worksheets, external databases, websites, and even Cloud services. Once connected, you can import data and perform a wide range of data transformations.

  • Data Cleaning: Clean up messy or redundant data. Power Query can help clean up by removing duplicates, correcting errors, and standardizing formats.
  • Data Transformation: Power Query lets you pivot, unpivot, merge, and append tables to get your data ready for analysis.
  • Columns: Add calculated columns or split existing columns. Similar to the way we do it in Excel, Power Query makes it easy to perform various operations.
  • Data Merging: Power Query can merge data from separate tables or files into a single large dataset for analysis.

Power Query records all the steps you take to transform your data so if your data changes, you can refresh your query with just a mouse click. In addition, Power Query is user-friendly. You don't have to be able to write code to use it effectively for example.

Power Pivot is a function within Microsoft Excel that allows you to create a Data Model and establish Relationships between tables just like a Relational Database. Power Pivot also lets you calculate KPI’s. As of early 2025, not all versions of Excel supported Power Pivot.

A diagram of a power pivot

 

To run the Power Query Editor from within Excel, place the cursor at cell A1 of the table or data range and select “Data | From Table/Range” as shown in the example below:

A screenshot of a spreadsheet Description automatically generated

 

This opens the table in the Power Query Editor. From here you can add or delete columns or rows, transform your data, remove any errors, or even calculate basic statistics. When you are done, just click “Close & Load” in the upper-left corner of your screen:

A screenshot of a computer Description automatically generated 

 

The screenshot below shows a Query imported into Power BI from Excel. We have added a column in order to calculate Total Value:

A screenshot of a computer Description automatically generated

 

It’s very easy to create charts, graphs, and even maps in Power BI. They are called “Visualizations.” If you already know Excel, most of your skills should transfer over. Ranges are defined much like in Excel. But there are many more styles of charts and graphs to choose from. Unlike Excel, the graphs in Power BI are interactive. Select a Visualization and choose the Data you want to display over on the right. The chart or graph shows up on the screen where it can be resized or edited as necessary. Save your work as a dashboard with a “.pbix” extension:

A screenshot of a computer Description automatically generated

Popular posts from this blog

Reliability Calculations in Excel

Image
In Operations Management today we used Excel to calculate product reliability. Reliability is defined as "the ability of a product, service, part, or system to perform its intended function under a prescribed set of circumstances" (Stevenson, 2009). In order to calulate reliability over a given length of time we need to use the following formula: P(no failure) = e -t/MTBF where: e = 2.7183 t = Length of service before failure MTBF = Mean Time Between Failures One of the cases we did in class involved an auto detailing company that wanted to calculate the reliability of its vacuum cleaners. Excel provides an "EXP" funtion that we can easily apply here. After entering all data for the problem as shown on the spreadsheet below, we type the formula =EXP(-A5/$B$2) in cell B5 and copy down. Next we plot the distribution for t=0 to the maximum life expectancy of 20 years using a line graph. What we end up with looks like a classic case of "ex...
Read more

EOQ Calculations in Excel

Image
Last week in Operations Management we used Excel to calculate the Economic Order Quantity and graph Carrying Costs, Ordering Costs, and Total Costs. The Economic Order Quantity or "EOQ" is the order size that "minimizes" Total Costs. Any more or less and you are spending too much on ordering or too much on keeping inventory. For example, in the Excel spreadsheet below, if you had an Annual Demand of 12000 units, Ordering Costs of $10 per order, and Holding costs of $4 per unit per year, the EOQ would be 245 units and Total Costs would be $980.00: We used the following formula in Excel to calculate EOQ: =SQRT((2*B2*B3)/B4) And the following formula to calculate Total Costs at this point: =$B$6/2*$B$4+$B$2/B6*$B$3 To create the graph, we used the following formulas and simply copied them over a range of 100 to 500 units. Ordering Costs: =$B$2/D2*$B$3 Holding Costs: =D2/2*$B$4 Total Costs: =F2+G2 Looking at this chart, we can clearly see that our order size of 245 is ...
Read more

Generative AI and the Future of Work

Image
2024  At least 40% of the global workforce has already been exposed to AI . Findings were skewed in favor of more highly developed economies (60%) vs emerging economies (26%) due in large part to the prevalence of white collar, service-oriented jobs in those regions – International Monetary Fund, 2024. 2025 Generative AI is projected to bring $200 billion USD to global education – Morgan Stanley, 2023. 2026  Over 75% of businesses will use generative AI to create synthetic customer data, up from less than 5% in 2023. Introducing synthetic data into models enables organizations to simulate environments and identify new product development opportunities, especially in highly regulated industries – Gartner, 2024.  2027 More than 50% of the generative AI models that enterprises use will be specific to either an industry or business function – Gartner, 2024. Six in ten workers will require retraining , but only half of workers are seen to have access to adequate training...
Read more