Adding Columns in Power BI Desktop

Power BI is used for the analyses of all the business-related factors and for calculating different factors which are of prime importance in business running. The data can be stored in separate tables where many columns can be used to represent different data types. In this article, we will learn how to create new columns in your report using Power BI Software.

Here we will discuss 4 types of Adding Columns in Power BI Desktop

• Add New Columns in Power BI
• Add a Custom Column in Power BI
• Adding Column In Virtual Table
• AddColumns DAX Tabular Function

Add New Columns in Power BI

AddColumns is a DAX function that is helpful often when writing calculations in Power BI. When adding a calculated column, we must use related data if we are working with more than one table. Let’s see the steps to add a new column in Power BI.

Step 1: You can start by opening the report you want to add a new column into.

Step 2: Click on the name of the report you want to add a new column into, right-click on the name and select the new column option to add a new column. 

Step 3: The next step is to add a new formula in the formula bar about the details to be displayed in the new column to be added. For example, if you want to merge the ‘Name’ and ‘Phone Number’ into one cell. You can add a new column as ‘New Column’ with the formula ‘New Column = [Name] & [Phone Number]’.

Step 4: Once the formula is added, press enters or click on the check mark present next to the formula bar to add the column. The new column will be added in the fields pane on the right side of the Screen and you can add it into your report whenever you need it.

Add a Custom Column in Power BI

By using a formula, you can extend your existing query to include a custom column. The Query Editing dialogue box and Power Query both validate the formula syntax.

Step 1: To work with a dataset, launch the Power BI Desktop application and add it to the workspace.

Step 2: Locate the table or query where the custom column will be added in the Power Query Editor. Click on “Edit Queries” or “Transform Data” in the Power BI Desktop’s Home tab to open the Power Query Editor.

Step 3: Choose the table or query where you wish to create the custom column in the Power Query Editor.

Step 4: Open the Power Query Editor ribbon and select the “Add Column” tab.

Step 5: Select “Custom Column” from the menu. A different option is to right-click on an existing column header and select “Insert Custom Column.”

Step 6: Enter a name for your customized column in the “Custom column” dialogue box that displays.

Step 7: The “Custom column formula” field is where you should provide the formula for your unique column. The computation or transformation you want to employ can be specified using the Power Query formula language “M”. For instance, you can use the expression =[Column1] & [Column2] to concatenate two columns.

Step 8: The custom column can be added by clicking the “OK” button.

Step 9: Check the custom column’s calculation by previewing the modifications. The Power Query Editor window displays the preview.

Step 10: To make the modifications effective and add the updated data to your Power BI report, click the “Close & Apply” option.

Step 11: You may now use the custom column in your Power BI report’s visualizations, computations, and any other required analyses.

Adding Column In Virtual Table

A calculated table can be created using AddColumns. However, employing it inside a measure is the most typical application. AddColumns is a tabular function, hence additional functions are required in order to use it in a measure.

Step 1: To work with virtual tables, use the Power BI Desktop application and choose the report or dataset.

Step 2: Make sure you are in either the “Data” view or the “Report” view, which will allow you to access the “Fields” pane and the “Formula bar.”

Step 3: To add a new measure or calculated column, click the “New Measure” button in the “Fields” pane.

Step 4: You can create virtual tables and add columns using DAX expressions and functions in the formula bar. Here’s an illustration:

VirtualTableWithColumns =
VAR VirtualTable =
    SUMMARIZE(
        SalesTable,
        SalesTable[Category],
        "Total Sales", SUM(SalesTable[SalesAmount]),
        "Avg Sales", AVERAGE(SalesTable[SalesAmount])
    )
RETURN
VirtualTable

In this illustration, a virtual table called “VirtualTable” is made from the SalesTable using the SUMMARISE function. The VirtualTable has three columns in addition to the “Category” column: “Total Sales” and “Avg Sales,” which are calculated using the SUM and AVERAGE functions, respectively.

Step 5: Enter will evaluate the formula and generate the fictitious table.

AddColumns DAX Tabular Function

A tabular function in DAX is AddColumns. It denotes that a table, not a value, is returned. Tabular functions must be buried inside other functions or utilized as a table expression before being used directly in a measure. The following syntax is required to use this function:

AddColumns(<table>, <column name1>, <expression1>, <column name2>, <expression2>, ...) 

Table: the table that we want to add columns to it
Name: the name of the newly calculated column.
Expression: the expression of the new calculated column you can add multiple columns using this function.

Step 1: Start the Power BI Desktop and open the report you want to work on.

Step 2: Right-click on the table name on the right side of the screen in the field pane and click on the new measures option to create a new DAX Measure.

Step 3: Enter the formula for the new DAX Measure you want to create which gives details about the information to be created for the new measure. The format for the formula is given above to add a new column.

Step 4: The name or reference of the existing table to which you want to add the columns should be substituted for the table.

Step 5: The names you choose for the new columns should be substituted for column name1, column name2, etc.

Step 6: Substitute the DAX expressions that determine the values for the new columns for expression1, expression2, etc. Existing columns or measures, mathematical operations, logical operations, text concatenation, and other DAX functions can all be used in these statements.

Step 7: Once the formula is written, press enters or click on the checkmark next to the formula tab to create a new DAX Measure.

Step 8: The new DAX Measure will be in the field tab on the right side of the screen, and you can use it as needed.