How to add a column with a dropdown list in Power Query

by

Do you ever know how to add a column with a dropdown list in Power Query? Don’t wait. This Microsoft Power BI Tutorial explains how to add a column with a drop-down list using Power Query.

We can add a column with a dropdown list using the Power Query editor in the Excel Spreadsheet, but it is not possible to add a column with a dropdown list using the Query editor in Power BI.

Power Query add a column with a dropdown list

Let us see how we can add a column with a dropdown list using the Power Query editor,

  • In this example, I have used the sales table data mentioned below. Initially, open the excel workbook and load the data into the excel sheet.
Power Query add a column with a dropdown list
Power Query add a column with a dropdown list
  • Now select any cell within the table and click on Data > From Table from the ribbon.
  • So that it will redirect to the Query Editor dialog to transform the data shown below:
Power Query add a column with dropdown list
Power Query add a column with dropdown list
  • Now we will remove the other columns using the query editor to update the query.
  • In the Power Query window, to remove other columns from the table, select the particular column and right click on it and choose to Remove other columns option to update the query as below:
= Table.SelectColumns(#"Changed Type",{"Product Name"})
Example of Power Query add a column with a dropdown list
Example of Power Query add a column with a dropdown list
  • Now right-click on the Product column and choose to Remove Duplicates option to remove the duplicate values that are presented in the table.
= Table.Distinct(#"Removed Other Columns")
Power Query add a column with a dropdown list example
Power Query add a column with a dropdown list example
  • Now sort out the product column, To Sort the list in ascending order select Sort ascending order as shown below:
= Table.Sort(#"Removed Duplicates",{{"Product Name", Order.Ascending}})
add a column with a dropdown list using Power Query
add a column with a dropdown list using Power Query
  • Now click on the Close and load option, so that the changes will be reflected in the excel workbook as below:
Power Query add a column with dropdown list example
Power Query add a column with dropdown list example
  • Now the new list appears in the excel workbook as a table as mentioned below:
add a column with a dropdown list using Power Query example
add a column with a dropdown list using the Power Query example
  • To make the new list as a dropdown list follow the steps below: Now select the Formulas > Name Manager option from the ribbon.
  • In the Name Manager dialog, we click the New button to create a new name and click on the ok button.
add a column with dropdown list using Power Query example
add a column with dropdown list using the Power Query example
  • To Create the drop-down select the input cell that should contain our drop-down, and then select Data > Data Validation.
  • In the Data Validation dialog, select the List option. The Source is equal to our name =ProductName, as shown below.
Example of add a column with a dropdown list using Power Query
Example of adding a column with a dropdown list using Power Query
  • In the below screenshot, you can see that the new column has been added with a dropdown list using the Power Query editor in excel Workbook.
add a column with a dropdown list using the Power Query
add a column with a dropdown list using the Power Query

This is how to add a new column with a dropdown list using the Power Query editor.

You may like the following Power query tutorials:

>