power query tutorial, power query tutorial for beginners, power query examples, power query guide, power query in excel 365, excel 2019 2016 2013 2010
What is POWER QUERY
Power Query is an automatic Engine introduce by Microsoft Corporation that write every step users do in Power Query and run the same steps when users refresh data. So users no need to do same steps everyday. Power Query is inbuild application in POWER BI and Excel 365, 2019, 2016. For Excel 2013 & 2010 users require to free download Power Query Addon package. Therefore, Power Query makes your daily excel work more FASTER SIMPLE EASY and AUTOMATIC.
35 Full Example
What is Benefits of POWER QUERY
In POWER QUERY, users can import file from various DATA SOURCE, perform DATA CLEANING, DATA TRANSFORMATION, DATA ARRANGING, DATA FORMATTING, make CALCULATION and prepare require data. What you done in POWER QUERY from first to end, Power Query automatically record each and every ACTION or STEP in directory. Power Query repeat all action or step, when users click on REFRESH button in future.
P.Q. Editor in Excel 365, Excel 2019, Excel 2016, Excel 2013 & Excel 2010
Step #1 - Go to DATA get DATA, P.Q. EDITOR.
This is Office 365, In Excel 2019 version, you can find the PQ Editor in same place. But Excel 2016 version, you will find the PQ Editor in Data Tab. And the 2013 & 2010 version, users need to install PQ Add-on and find the PQ Editor in PQ tab.
How to Insert or Pull or Import Excel Sheets and Files
Step #1 - Go to New Source > File > Excel.
Step #2 - Select the File and press on OK
Step #3 - Select the sheet or multiple sheet & press on Import. Our data is loaded.
How to Check Header, Remove Top ROWs, and Select Header
Sale Invoice header is correct and all salary sheet, there are no problem in header.
But in the Employee master, there are problem in Header.
This is our header in ROW 2, so I need to remove the ROW 1.
Step #1 - Go to the REMOVE ROW > REMOVE TOP ROW, and how many row I need to remove i.e. 1.
Step #2 - Because in the second row, I have the header. OK. Now header is in Row no 1.
Step #3 - Select this option, USE FIRST ROW AS HEADER. Our header is selected.
How to Rename Column Header and Rename Table Name
Step #1 - In the Employee Master, if you need to change the table name, double click or
Step #2 - Press F2, or change from here.
Step #3 - I rename it as Employee Data and enter. You Notice, name has been changed.
Step #4 - And if you need to rename the column, select the column first.
Step #5 - I need to select STATE. Yes, there is a problem.
State, State two times. Double click on it & rename it STATE, press enter.
How to Remove Empty or Null Rows and Error Rows
Step #1 - Select the column, click on FILTER option, & you find that there is a NULL value
Step #2 - Means the empty data. Unselect the null value.
Step #3 - There is another Not Application "NA" Value
Step #4 - Unselect this NA value and check it. Now there are no null value and NA value
How to remove extra space by use TRIM function in PQ
Step #1 - Select the COUNTRY & click on FILTER option.
Step #2 - You find IND, IND, INDIA. I need to replace IND to INDIA.
Step #3 - But IND shows two times because there are some space. OK
Step #4 - Select this column, click the RIGHT mouse button & TRANSFORM > TRIM
It is same as Excel, it will remove the extra space.
How to Replace Values
Step #1 - Go to the filter, now IND, INDIA and USA. I need to change IND to INDIA.
Step #2 - Select the column > REPLACE VALUE. I am looking for IND & what need to change INDIA.
Step #3 - Go to the ADVANCE option & MATCH ENTIRE CELL CONTENT.
Because there is entire call content is IND. If I not select the MATCH ENTIRE CALL CONTENT. Where IND are there, it will be change this one also.
Step #4 - So MATCH ENTIRE CELL CONTENT, ok. Now go to the filter, you notice, it is shows INDIA & USA.
How to Rename, Remove/Delete, UpWard, Downward and manage of the PQ Steps
Every step you do here, POWER QUERY write the step. You can delete the step by clicking on cross button, or you can move the step upward or downward. Also be you notice, some steps are the GEAR icon, you can click on GEAR icon & change the step. RIGHT click on the step, you can notice RENAME, DELETE, MOVEUP, MOVEDOWN & other option are there.
How to Remove Column and Choose Column which is not required
Step #1 - Go to the SALES INVOICE table & here I need only EMPLOYEE ID & INVOICE VALUE
Step #2 - So select the EMPLOYEE ID & select the INVOICE VALUE, Go to REMOVE COLUMN > REMOVE OTHER COLUMN.
Step #3 - I need the EMPLOYEE ID & INVOICE VALUE to calculate the Incentive.
Step #4 - Again go to the employee table, I need the EMPLOYEE ID, FISRT NAME & LAST NAME
Step #5 - I go to the CHOOSE COLUMN, untick all, choose EMPLOYEE ID, FIRST NAME, LAST NAME & COUNTRY, OK
How to Check and Correct Data Type in PQ
This is very important topic, DATATYPE. I will make video on DATATYPE, for now I simply check the DATATYPE. PQ already automatically fetch the data type. Again I need to check the DATATYPE.
Step #1 - Users can click on small DATATYPE box in each column and change the datatype.
Step #2 - If users need to change multiple column datatype, Select the column, HOLD SHIFT key & select continuous column or HOLD CTRL key to select the miltiple column.
Step #3 - Go to the TRANSFORM > DATATYPE & change it to decimal number. It will changed the every column. DO this same.
How to Create and Manage Query Group for DATA MODELING in PQ
This is require for the DATA MODELING.
Step #1 - Right click on the space and select NEW GROUP & name it SOURCE DATA. OK
Step #2 - These 7 or 8 files are the source data, I need to transfer to SOURCE GROUP.
How to Combine Different Month Salary excel sheet into one table by Append Queries & Append Queries as New in PQ
Step #1 - I need to select the January month, go to the HOME, select COMBINE
Step #2 - And APPEND QUERIES AS NEW. What is the meaning of the APPEND QUERIES?
If you have the similar table, you can marge then together by the APPEND QUERIES.
Step #3 - APPEND QUERIES AS NEW, I have the six table, therefore, I need to select THREE OR MORE
Step #4 - January, double click on it, February, March, April, May & June, press on Ok.
How to Difference Between MARGE QUERIES and APPEND QUERIES in PQ
You can find, in the combine tab, there are two option, MARGE QUERY & APPEND QUERY. When I choose the MARGE QUERIES and when I choose the APPEND QUERIES
Find ADVANCE article on this topic
How to Create a Reference Table. What is the Reference Table
Step #1 - I will create a REFRENCE table of the SALE INVOICE
Step #2 - Right click on the SALE INVOICE table and create the REFRENCE TABLE.
Step #3 - If I make any changes in the source data, it will change the sale invoice table,
Step #4 - Also be change in the REFR4ENCE TABLE. I will create a REFRENCE TABLE.
Step #5 - And rename it INCENTIVE DATA and ENTER.
How to Calculate One Employee Total Salary by GROUP BY in PQ
Step #1 - This is our total salary & there are 75 Rows for the 3 month 25 for each, total is 75 rows.
Step #2 - I need to create TOTAL SALARY of one employee. Here you can change the DATA TYPE to TEXT.
Step #3 - Select one EMPLOYEE ID, copy it, go to the FILTER, paste it. One employee shows counts three (3) times. Why?
Step #4 - Becasue One employee present in January, February and March month salary table
Step #5 - Select the table, select the EMPLOYEE ID, go to GROUP BY option. And Employee ID selected.
Step #6 - Go to ADVANCE, ADD GROUPING & select the SALARY STRUCTURE. I need to create total salary of one employee.
So NET SALARY, SUMMATION, and summation of NET PAY.
How to Calculate Incentive with ADD CUSTOM COLUMN in PQ by using M LANGUAGE
Step #1 - This is our INCENTIVE TABLE, EMPLOYEE ID are there, INVOICE VALUE are there.
Step #2 - I need to create the INCENTIVE amount.
Step #3 - Go to the ADD COLUMN > choose CUSTOM COLUMN and rename the INCENTIVE AMOUNT
Step #4 - INVOICE VALUE * 0.05, 5% incentive and there is a tick option. No Error in syntax.
Fine, this is our INCENTIVE AMOUNT.
How to make change or update steps by GEAR button
Before that, I need to make some changes in previous step.
Step #1 - Go to the SALE INVOICE TABLE and go to the REMOVE OTHER COLUMN STEP.
Step #2 - Click on the GEAR Icon and here I selected the EMPLOYEE ID & INVOICE VALUE.
Now I need to select the BILL DATE and press OK.
What is the reason to select the BILL DATE?
If I not get the BILL DATE, I can't identify the INCENTIVE related to which month. So I need the BILL DATE.
How to create DUPLICATE column
Step #1 - Here I click on the bill date and right click on it. Create a DUPLICATE COLUMN.
Step #2 - Double click on it, rename it as MONTH.
How to Convert DATE to MONTH NAME, YEAR, MONTH NUMBER, WEEK, QUARTER, DAY
Step #1 - Go to the ADD COLUMN and here you can find the DATE option, click on it.
Step #2 - Choose the option MONTH > select NAME OF THE MONTH
You notice that new column added for the month. January, February and March are there.
How to Combine Total Salary Table & Incentive Table – MARGE QUERIES
Step #1 - Select the EMPLOYEE WISE TOTAL SALARY table, go to COMBINE option and select MARGE QUERIES AS NEW
Step #2 - EMPLOYEE WISE TOTAL SALARY, select the EMPLOYEE ID
Step #3 - And select the EMPLOYEE WISE TOTAL INCENTIVE & select the EMPLOYEE ID. This is almost similar VLOOKUP in excel, I select the common column of both table.
Step #5 - For now, JOIN KIND > LEFT OUTER, all from the first and matching with second.
Step #6 - Press on the OK BUTTON and new table created. Rename it "SALARY INCENTIVE" enter.
Step #7 - Go to the end of this table and employee wise INCENTIVE column selected.
Step #8 - Click on this button, untick ALL COLUMN option
Step #9 - Tick TOTAL INCENTIVE, OK.
How to Calculate Total Payout means Salary + Incentive – M LANGUAGE
Step #1 - Go to the ADD COLUMN, choose the CUSTOM COLUMN
Step #2 - Rename it SALARY INCENTIVE.
Step #3 - Select the NET SALARY, double click and put the plus + sign and double click on TOTAL INCENTIVE.
Step #4 - Change the data type to decimal number
How to ROUNDING UP and REMOVE NUMBER after decimal point in PQ
Step #1 - You notice that, after dot, there are 3 digit but I need 2 digit.
Step #2 - Go to the TRANSFORM, select the ROUNDING > ROUND, and decimal place two (2). OK
Step #3 - Now after dot, decimal place is two (2)
How to Combine / Marge Text or TWO COLUMN VALUE as Employee Full Name M LANGUAGE
I already published an article on this topic and you can marge two column data into one column by 3 process, please find the link.
Step #1 - Go to the EMPLOYEE DATA, I need to create the FULL NAME
Step #2 - Go to the ADD COLUMN > CUSTOM COLUMN. Rename it FULL NAME
I use the easy method to create the FULL NAME
Step #3 - Choose the FIRST NAME, use the "&" ampersand option, DOUBLE QUOTE, maintain the space between, again ampersand "&" and use the LAST NAME.
Step #4 - Press on OK.
Step #5 - Change the DATATYPE to text.
How to Insert Employee Name in Final Table by MARGE QUERIES
This is our FINAL TABLE. Here is the EMPLOYEE ID, EMPLOYEE NAME not here. I need to put the EMPLOYEE NAME in this table.
Step #1 - Select the table, go to HOME > COMBINE > MARGE QUERY
Don’t select the MARGE QUERIES AS NEW, because I need to put the value in this table.
Step #2 - Select the EMPLOYEE ID, go to the employee data table, select the EMPLOYEE ID
Step #3 - This the common column in both table, LEFT OUTER JOIN and press on OK.
Step #4 - Select the icon and untick the ALL option and select the FULL NAME.
This is the name of the employee.
How to Select Multiple Column & Arrange Column in PQ
Step #1 - Select the multiple column from 1 to 3. Click 1 column & Hold the Shift Key and click on column 3. All three column are selected.
Step #2 - Select employee id & net salary, press the CTRL key and select the NET SALARY, both column selected. Salary Structure is not selected.
And now I want to arrange our data.
Step #3 - After the employee id, I need to put the employee name. Right click on it, go to the MOVE > TO THE BEGINNING
Step #4 - I select the Employee ID column and move to the first.
Step #5 - NET SALARY, GORSS SALARY, GROSS SALARY should come first.
Column arrange as GROSS SALARY, NET SALARY, TOTAL INCENTIVE AND SALARY PLUS INCENTIVE.
How to Export or LOAD Data from POWER QUERY Editor to Excel – Data Connection
Step #1 - Go to the CLOSE AND LOAD option and click on the small arrow
Step #2 - And select the CLOSE AND LOAD TO.
Step #3 - Now POWER QUERY asking you, how you want to export data to Excel
Step #4 - In the table format, pivot table, pivot chart or connection
Step #5 - I select the CONNECTION option
Step #6 - Press on OK
POWER QUERY difference between Excel 365 Excel 2019, Excel 2016, Excel 2013 and Excel 2010
Go to DATA get DATA, P.Q. EDITOR.
This is Office 365, In Excel 2019 version, you can find the PQ Editor in same place. But Excel 2016 version, you will find the PQ Editor in Data Tab. And the 2013 & 2010 version, users need to install PQ Add-on and find the PQ Editor in PQ tab.
PIVOT TABLE and PIVOT CHART are not available for EXCEL 2016, 2013 & 2010
But you can find this two option in older version of the excel
Tick this option and press on OK.
How to Hide un-Necessary Group in PQ for DATA MODELING
No need to populate the source data, source data are available in my excel file.
Just click on the arrow button and For that reason I create the DATA GROUP
Different Refresh Option in PQ
Step #1 - Go to the DATA, you can find the REFRESH ALL option
Step #2 - Means if I select the refresh all, all data will be refreshed in POWER QUERY
Step #3 - If I select any of the query or sheet & press refresh, only this query will be refreshed
Step #4 - Also you can refresh from here, right click on it and press the refresh
How to ADD, REMOVE, DELETE Table from MARGE or COMBINE table in PQ
Step #1 - Go to the query editor > Get Data > Launch Query Editor or you can double click on any table to go Query Editor
Step #2 - And you want to remove some month salary from our final data
Step #3 - Go to TOTAL SALARY, go to SOURCE, if you want to remove MARCH month salary, you can DELETE the march
Step #4 - Press on OK. It will deleted the March month SALARY from the FINAL DATA.
- [message]
- ##check## Download project file and Try Yourself.
- Please download the project file and sperate different excel sheets to sperate the excel file and start the project as described in the tutorial video. If you face any problem, please let me know in below comment box.
COMMENTS