Tutorial Playlist
68 Lessons1. Excel Tutorial
2. Excel Worksheet for Practice
3. Google Sheets Vs. Excel
4. Excel Shortcut Keys
5. Excel Keyboard Shortcuts
6. Excel AutoSum Shortcut
7. Redo Shortcut in Excel
8. Charts in Excel
9. Pivot Charts in Excel
10. Excel Sum Formula
11. Excel Percentage Formula
12. Excel Age Calculation Formula
13. Excel Range Formula
14. Round Off Formula in Excel
15. VLOOKUP Formula in Excel
16. Excel Transpose Formula
17. Average Equation in Excel
18. How to Use DATEDIF Formula in Excel
19. IRR Formula in Excel
20. Standard Deviation Formula
21. Excel Age Calculation Formula
22. Excel MAX Function
23. Excel LEFT Function
24. Excel RIGHT Function
25. Trim Function In Excel
26. LookUp function in Excel
27. Columns in Excel
28. How To Sort Data In Excel
29. How To Freeze Panes in Excel
30. Page Setup in Excel
31. How to Recover an Unsaved Excel File
32. Concatenate in Excel
33. Count In Excel
34. IF Condition In Excel
35. H LOOK UP in Excel
36. How Do You Move Columns in Excel
37. Split Cells in Excel
38. Remove Blank Rows in Excel
39. How To Lock Cells in Excel
40. Data Validation in Excel
41. How to Insert Checkbox in Excel
42. How To Highlight Duplicates in Excel
43. Fill Series in Excel
44. How to Create Excel Drop-Down List
45. What-If Analysis in Excel
46. How to Use SUMIFS Function in Excel
47. INDIRECT Function in Excel
48. Pivot Table in Excel
49. Slicers in Excel
50. How to Create a Dashboard in Excel
51. Excel Data Cleaning
52. Data Analysis In Excel
53. Goal Seek in Excel
54. Solver in Excel
55. Power Query in Excel
Now Reading
56. Macros In Excel
57. How To Make Graph in Excel
58. How To Make Histogram In Excel
59. How To Convert PDF to Excel Without Software
60. Barcode Font For Excel
61. Gantt Chart in Excel
62. Excel RANK function
63. AND Function in Excel
64. How to Calculate NPV
65. Format Painter in Excel
66. Count Colored Cells in Excel
67. Amortization Schedule in Excel
68. Master Cell References in Excel
Power Query in Excel is an add-on tool specializing in cleanly presenting your data. Although it might sound like something that Excel would do, it is certainly different than that.
With Power Query, you can sit back and relax while the tool does its job. When you feed it data, the Power Query tool creates a smart filter for all your items. The result? You see all your data but in a more presentable way.
Know what you can do with this tool. Here’s more on Power Query and its quite powerful ways!
Power Query in Excel is another term for Get & Transform, a feature made to overtly manipulate data within a fraction of a second. How does it achieve this?
Power queries usually work in four different ways—all part of the same goal—data acquisition, data cleaning, data combining, and data loading. The end product?
Power Query lets you quickly access data and subsets of data. This tool does not discriminate, so your skill level does not matter.
But let’s face it— you can’t use Excel efficiently without using all the shortcuts and hacks provided. That’s exactly where Power Query comes in.
This is also why you have an option to choose. This add-on brings a fresh breeze of air into your already cluttering sheet full of data.
Here’s why Power Query works out: It processes a vast dataset from websites, databases, or wherever you need to source the information.
Want to know what’s more interesting? Power Query is user-friendly, which is, perhaps, its best feature so far. For Excel users, there’s an option to drag and drop and clear menus instantly. Whether you're an expert or just starting out, this add-on tool has got your back.
Last but not least, this tool even lets you write custom functions in your own time, giving you more control over your data output.
Beyond everything, the main function of this tool is to present valuable insights in the form of data so you can make better decisions.
As someone who spends a lot of time in Excel, I know how messy data can be a pileup sometimes. Here's why Power Query has become my go-to tool for data manipulation:
When working with Excel’s Power Query tool, remember the "connect, transform, combine, and load" strategy.
Here's a breakdown of each stage, along with the commands you can use (though the process can often be done visually as well):
Source - Enterprise DNA Blog
Let’s say you’re working on a sales report in a text file and customer information in a separate Excel workbook. Power Query helps you connect to these sources:
Connect to Text/CSV file: Data tab > Get Data > From Text/CSV > Browse to your file and click Import.
Connect to Excel workbook: Data tab > Get Data > From Workbook > Browse to the workbook and select the specific sheet containing your data.
Here are some commands for common transformations:
Remove duplicates: Select your table > Home tab > Remove Duplicates (choose the column(s) to identify duplicates).
Standardize formatting: Select a column > Transform tab > Data Type (choose the appropriate data type, e.g., Text, Date).
Filtered data: Select your table > Home tab > Filter Rows (Define your filtering criteria).
If you have data from both the text file and Excel workbook, you can combine them:
Merge Queries: In the Home tab, click Merge Queries > Merge Tables (Choose the tables to merge, define the join key columns).
Once your data is ready, click Close & Load (you can choose to load it as a table or pivot table).
You will find that Power Query is installed by default as an add-on in all Excel versions from 2016 onwards. When you end up finding it, here are a couple of ways how to open Power Query in Excel.
Source - YouTube
Step 1. Open your Excel worksheet.
Step 2. Search for the Data tab on the ribbon. Should you notice the Data tab with extended options like Get Data and Transform Data, your Power Query is already activated.
Step 1. Open the Excel worksheet.
Step 2. Go for the File tab and select Options.
Step 3. Click on Add-Ins as it appears in the Options window on the left sidebar.
Step 4. Click on COM Add-ins and then on Go in the Manage menu.
Step 5. A list of add-ins will appear. Look for Microsoft Power Query for Excel.
Step 6. If the checkbox next to Power Query is not selected, check it to enable it.
Step 7. Click OK.
Step 8. Restart your Excel application and find the now-activated tool in the Data tab.
While the visual interface is user-friendly, a Power Query download offers the M Language for advanced transformations. Here's an example for removing duplicates using M:
let
Source = Excel.CurrentWorkbook(),
#"YourTableName" = Source{[SheetName="YourSheetName"]}[#"YourDataTable"],
UniqueTable = Table.Distinct(#"YourTableName")
in
UniqueTable
Here's an example of how I use Power Query in Excel:
Scenario: You have a sales report to publish. There are thousands of rows of data in the text file. The file seems messy as there are heaps of data, some duplicates and others formatted differently scattered throughout.
Take it from the Data tab itself and choose Get Data > From Text/CSV. Navigate to the text file and click Import. Power Query displays a preview of the data.
You can also call this process data wrangling. If you see duplicate entries, you select the table and head to the Home tab. With a click of the Remove Duplicates button, those duplicates vanish in no time.
To fix inconsistent date formats, select the date column, go to the Transform tab, and choose the appropriate Data Type. Power Query instantly formats all the dates consistently.
Once you’re satisfied with the cleaned data, it's time to bring it back into Excel. Click Close & Load in the Power Query Editor. Power Query imports the transformed data as a new, clean table in my worksheet.
If you’re interested in data analysis, Excel’s Power Query would be your best match. Apart from several tools that you can use to ease your job, you will find that this tool carries some weight to it. Let me explain how.
In data analytics, this function will greatly improve your ability to work and manipulate data. With Excel’s Power Query tool, you don’t have to manually handle the updates anymore. Just automate inputs, and you’re good to go.
In terms of business intelligence, transforming data is a vital step before moving into another important facet. Power Query is a good place to start if you’re interested in regular insights.
In general, here’s a list of options for which you can use Excel Power Query:
In short, if you want to work effectively without creating a pile of work, this is the tool for your needs.
We have established how Power Query can shine as your go-to tool for Excel data. From managing duplicates to filtering and merging data, this Power tool can be of immense help.
If you are looking for industry-standard Excel courses, upGrad can be the best place to get your certification and hands-on experience from leading faculty.
Check out the website and get your resume to shine! Enroll in your desired course today!
Using the Power Query Editor in Excel is as easy as opening a jar of olives. Go to the Data tab>Get Data. Choose your data source i.e. File or Text/CSV. Following this, clean or reshape your data using the ribbon or formula bar. Once your data is ready, you can close and load it into your Excel sheet.
Excel Power Query can have two functions —the built-in Power Query formula language functions and custom functions.
Excel and Power Query are related in terms of data analysis. While Excel provides a platform for conducting data analysis, Power Query is a data manipulation tool within Excel.
The Excel query function offers a way for users to write queries using the Power Query Formula language, which provides a powerful way to filter, sort, and transform your data.
You can summarize the basics of Excel Power Query in four ways —connecting to your data source, transforming data, combining data, and loading data.
To add a Power Query in Excel, you can use the Power Query Formula Language (M). To filter data, enter the code - Table.SelectRows(#"YourTableName", [column1] > 10). Similarly, to remove duplicates, use this code - Table.Distinct(#"YourTableName").
Devesh
Passionate about Transforming Data into Actionable Insights through Analytics, with over 3+ years of experience working in Data Analytics, Data V…Read More
Talk to our experts. We’re available 24/7.
Indian Nationals
1800 210 2020
Foreign Nationals
+918045604032
upGrad does not grant credit; credits are granted, accepted or transferred at the sole discretion of the relevant educational institution offering the diploma or degree. We advise you to enquire further regarding the suitability of this program for your academic, professional requirements and job prospects before enrolling. upGrad does not make any representations regarding the recognition or equivalence of the credits or credentials awarded, unless otherwise expressly stated. Success depends on individual qualifications, experience, and efforts in seeking employment.
upGrad does not grant credit; credits are granted, accepted or transferred at the sole discretion of the relevant educational institution offering the diploma or degree. We advise you to enquire further regarding the suitability of this program for your academic, professional requirements and job prospects before enr...