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:

Power BI add column with a fixed value
Power BI add column with a fixed value

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.
Power BI add column with a fixed value example
Power BI add column with a fixed-value example

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.
Power query add column with the constant value
Power query add column with the constant value
  • Once it is redirected to the Power Query editor, under the Add column option, select the Custom column as highlighted below:
Power query add column with a constant value
Power query add column with a constant value
  • 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"
Power query add column with constant value
Power query add column with a constant value
  • 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.
Example of Power query add column with the constant value
Example of Power query add column with the constant value

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

See also  Power BI Dax Min Function [With 15 Examples]

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).
Power bi add column with a fixed date
Power bi add column with a fixed date

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:
Power bi add column with fixed values on the day of the week
Power bi add column with fixed values on the day of the week
  • 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.
Power bi add column with fixed values on the day of the week example
Power bi add column with fixed values on the day of the week example

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

See also  How to Convert Text to Number 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.

add column with a fixed value count in the Power BI
add a column with a fixed value count in the Power BI

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:
Power bi add column with fixed values distinct count
Power bi add column with fixed values distinct count
  • 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.
Power bi add column with fixed values distinct count example
Power bi add column with fixed values distinct count example
  • 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}})
Example of Power bi add column with fixed values distinct count
Example of Power bi add column with fixed values distinct count

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

See also  Power bi Dax Today() Function [With real examples]

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:
Power Bi replaces column with a formula
Power Bi replaces column with a formula
  • 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:
Power Bi replaces column with a formula example
Power Bi replaces column with a formula example
  • 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"})
Example of Power Bi replaces column with a formula
An example of Power Bi replaces column with a formula

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:

>