Step 10. To load the data into the Excel worksheet, go to Home in the Query Editor Ribbon We can work more on this data or prepare compelling reports by adding (loading) it into the Excel worksheet. The Editor adds a new column to the data, as shown below. To add the formulated data to the existing one, click the Add Column tab in the Editor Ribbon, enter the New column name as Margin%, the Custom column formula as /, and click OK. We want to find the Margin in percentage therefore, we will use the formula / and add the formulated values to a new column. Here, we can give a name to the new column and enter a formula to provide calculated values.

It will allow you to create a new calculated column based on the custom formula.Ī Custom Column window appears on the screen. Step 8. To add a new column to the data, click Add Column > Custom Column under the General Group. We can add a column with computed/ calculated values to the existing data. The values in the Date column have now changed to the proper format, as shown below. Step 7. A new window, Change Column Type, appears on the screen. Step 6. To convert the date into the proper format, select and right-click on the Date column header, then select Change Type and click Date. In the transformed data, the date is not in the proper format, as shown below.

The Power Query Ribbon has five options- File, Home, Transform, Add Column, and View. Once you click the Transform Data tab, the Power Query Editor window opens with the transformed data. Cancel– It cancels or nullifies all other operations under the Power Query.Ĭlick on the Transform Data tab.You can also add a column with calculated values, change the format of specific columns, add or remove columns, group columns, etc. Transform Data- It allows you to transform the data files irrespective of the source.Load- It allows you to load the data as a table/Pivot into an Excel sheet, irrespective of the actual format of the data under the source file.However, it has no separate Edit option, making it less versatile as you cannot decide which columns to combine. Combine- It allows you to combine or consolidate datasets from different sources having different file types.The window has four options at the bottom- Combine, Load, Transform Data, and Cancel.

Noted: Power Query lists all the files irrespective of the file type. TEXT and String Functions in Excel (26+).Lookup and Reference Functions in Excel (36+).Excel Conditional Formatting in Pivot Table.

Power query excel 2013