Want to protect your work from prying eyes? Learn how to add a password to your Excel workbook.
1. Importing Data From Multiple Files
Using Power Query, you can import data from different files in a single folder. Be mindful, though, as you should still ensure all the data follows the same format. Keep the number of columns and the column header names the same and ensure the data type for the values in each column is consistent. For this guide, we are using five files that comprise ~100 million rows of data from Kaggle. Download them if you want to follow along or use your own data. With that in mind, here are the steps to import the data:
2. Filtering the Imported Files and Rows
In the Power Query Editor, you can apply filters to import only the files that match the criteria you set, which is very useful for eliminating data sources you don’t want. Tip: when working with spreadsheets in Microsoft Excel, it may be handy to know how to move a column.
3. Loading Filtered Data Into a Data Model
You can continue cleaning and filtering the data, but let’s move on and load it into a data model to start analyzing it.
Using the Power Pivot Data Model in Excel
4. Analyzing Data With PivotTable
After you’re done making changes to the data model, it’s time to analyze the data in an Excel worksheet using PivotTable. Dealing with many duplicates in your Excel workbook? Learn how to find and remove duplicates and clean up your spreadsheets.
5. Presenting Your Data With PowerChart
You can create more worksheets to analyze your data in different ways. This time, let’s create a more visually impressive result using PowerChart. Want to brush up on Excel fundamentals? We have a comprehensive guide on all the keyboard shortcuts for Excel. Image credit: Pexels. All screenshots by Brandon Li. DAX’s main advantage over Excel formulas is that it is designed to operate on aggregate data. Standard Excel formulas can perform calculations on a row-by-row basis (such as calculating the sum of multiple values in a row), but DAX formulas can operate on a column-by-column basis (such as counting the number of a specific value in a column), or even on an entire table. On the right side of Excel, click the “Queries & Connections” icon, then find and double-click on your query data source to open the window where you can modify the table.
