How to add column with a fixed value in Power BI

If you are looking out to know how to add column with a fixed value in Power BI? This tutorial helps you to set the fixed value column in Power BI. Also covered the below-mentioned headings :

1. Power BI add column with a fixed value
2. Power query add column with a constant value
3. How to add column with a fixed date in Power BI
4. Power bi add column with fixed values on the day of the week
5. Power bi add column with a fixed value count
6. power bi add column with fixed values distinct count
7. Power Bi replaces column with a formula

Add a column with a fixed value in Power BI

Let us see how to add a column with a fixed value or static value in a column in Power Bi,

In this example I have used the vehicle table data as mentioned below, here I have to add a new column with the fixed values in all rows:

Follow the below steps to add a column with a fixed value:

• Open the Power BI Desktop, load the data, select the Modelling tab, and click on the New Column option to create a column.
• In this example, I am going to add the fixed value called DK0010 in all the rows presented in the table, For that apply the below-mentioned formula in the formula box and click on the check icon:

Fixed Value = "DK0010"

Where,

• Fixed Value  = new column name
• DK0010 = Static String value

• The screenshot below shows that the newly created column displays the fixed or static value passed.

This is how to add a column with a fixed value or static value in a column in Power Bi.

Read How to add column from another table in Power BI

Power query add column with constant value

Let us see how we can add a column with the constant value using the Power Query editor in Power BI,

• Open the Power BI desktop, and load the table data into it, In the ribbon, under the Home option click on the Transform data option as shown below:
•  So that it will redirect to the power query editor, where we can add custom columns to the table.

• Once it is redirected to the Power Query editor, under the Add column option, select the Custom column as highlighted below:

• Now, enter the New column Name, and in the formula window, passes the constant value in it and click on the ok button as shown below:

="USA"

• In the below screenshot, you can see that the custom column has been added with the constant value using the Query editor.

= Table.AddColumn(#"Changed Type", "Country", each "USA")

• Click on the Close and Apply option from the ribbon so that the changes will be reflected on the Power BI desktop.

This is how to add column with the constant value using the Power Query editor in Power BI.

Read Power Query Add Column

Add a column with a fixed date in Power BI

Let us see how we can add a column with a fixed date value in a column in Power Bi,

In this example, I’ll add today’s date as a fixed date for all the rows presented in the table.

• Open the Power BI Desktop, load the data, select the Modelling tab, and click on the New Column option to create a column.
• In this example, I am going to add the fixed date as today’s date so that it will change the mentioned date for all the rows presented in the table, For that apply the below-mentioned formula in the formula box and click on the check icon:

Fixed Date = TODAY()

Where,

• Fixed Date  = new column name
• TODAY = Dax function

• The screenshot below shows that the newly created column displays the fixed date value. (ie., today date is 2022/10/13 – yyyy/mm/dd format).

This is how to add a column with a fixed date value in a column in Power Bi.

Add column with fixed values on the day of the week in Power BI

Let us see how we can add a column with fixed values on the day of the week using the power query editor in Power BI,

• Open the Power BI desktop, and load the table data into it, In the ribbon, under the Home option click on the Transform data option, So that it will redirect to the power query editor, where we can add custom columns to the table.
• Once it is redirected to the Power Query editor, under the Add column option, select the Custom column.
• In the custom column window, apply the below-mentioned formula and click on the Ok button as shown below:

• In the below screenshot, you can see that the custom column has been added with the day value count using the Query editor.

= Table.AddColumn(#"Added Custom", "Custom", each Date.DayOfWeek(#date(2022, 10, 13)))

• Click on the Close and Apply option from the ribbon so that the changes will be reflected on the Power BI desktop.

This is how to add column with fixed values on the day of the week using the power query editor in Power BI.

Read How to Filter Power BI Dax Based On Condition

Power bi add column with a fixed value count

Let us see how we can add a column with a fixed value using the count function in Power Bi,

In this example, I have used the count function based on the fixed column value.

• Open the Power BI Desktop, load the data, select the Modelling tab, and click on the New Column option to create a column.
• Now select the fixed value column using the count function, For that apply the below-mentioned formula in the formula box and click on the check icon:

Fixed Value Count = COUNT(Cars[Car Names])

Where,

1. Fixed Value Count = New column name
2. COUNT = DAX Function
3. Cars = Table Name
4. Car Names = Existing Column name

The screenshot below shows that the newly created column displays the fixed or static value Count of the cars.

This is how to add a column with a fixed value using the count function in Power Bi.

Power bi add column with fixed values distinct count

Let us see how to count distinct values for fixed values column using group by feature in Power BI,

• In the Power BI desktop click on the Transform Data from the ribbon, which redirects to the power query editor.
• Select the Country column and click on the group by option from the Power Query editor ribbon as shown below:

• Then the Group By window will appear, Click on the Basic options -> choose the Country column.
• Next, enter the new column name, select Operation as Count Distinct Rows, and then click on the OK button.

• In the below screenshot, you can see that the new column displays the count of the distinct values presented in the table.

= Table.Group(#"Added Custom", {"Country"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}})

This is how to count distinct values for fixed values columns using group by feature in Power BI.

Read How to Append Columns in Power Query

Power Bi replaces column with a formula

Let us see how we can replace column with a formula using the power query editor in Power Bi,

In this example, I will replace the country value as the USA and replace the value as Canada in Power BI,

• In the Power BI desktop click on the Transform Data from the ribbon, which redirects to the power query editor.
• Right-click on the Country column and select the Replace values as shown below:

• In the Replace value window, enter the value to find the value and replace the value with the value click on the Ok button as below:

• In the below screenshot, you can see that the new column displays the value with the replaced value.

= Table.ReplaceValue(#"Added Custom","USA","Canada",Replacer.ReplaceText,{"Country"})

This is how to replace a column with a formula using the power query editor in Power Bi.

This Power BI tutorial demonstrated to add a new column with a fixed value in Power Bi using the Power Query editor. Also covered the below-mentioned topics:

• Power BI add column with a fixed value
• Power query add column with the constant value
• Power bi add column with a fixed date
• Power bi add column with fixed values on the day of the week
• power bi add column with a fixed value count
• power bi add column with fixed values distinct count
• Power Bi replaces column with a formula

You may like the following Power BI tutorial:

• How to merge columns in Power Query
• How to Filter Date using Power BI DAX
• Power BI Slicer Multiple Columns
• How to Merge Column in Power BI
• Power BI Create Table From Another Table