PowerApps Cascading Dropdown (Detailed tutorial)

In this PowerApps Tutorial, We will discuss what is Cascading Dropdown in PowerApps, How to create Powerapps Cascading Dropdown using the SharePoint List.

Also, We will see these below things related to PowerApps Cascading Dropdown as:

• Cascading dropdown in PowerApps using SharePoint data (lookup column)
• Cascading dropdown in powerapps (Choice column)
• How to sort the Cascading Dropdown Value in PowerApps
• Create PowerApps Cascading Dropdown using the Sharepoint Lookup
• How to set the Cascading dropdown default value to blank in Powerapps
• PowerApps cascading Dropdown excel

By taking some different scenarios, I will explain how to use the PowerApps cascading dropdown easily. By the end of the tutorial, you will also be able to know how to use the PowerApps dropdown control.

Cascading dropdown in PowerApps using SharePoint data

Let us see a simple example of cascading dropdown in powerapps using sharepoint data.

Here I have two lists:

• Employee Country
• Employee State

The Employee Country list is having only one Title column and it looks like below:

The Employee State list has two columns:

• Title
• Country – It is a lookup column to the Employee State list.

Now let us create our Power Apps app, we will create a canvas app.

If you are new to Power Apps? Check out an article on How to use Power Apps for free

Step-1: Create your canvas app

Go to PowerApps.com and sing in with your Microsoft 365 credentials.

Then from the home page, click on + Create -> Canvas app from blank. The provide a App name and choose the layout, here I have choosen Tablet layout.

This will create a blank canvas app with a blank screen (tablet layout).

Step-2: Connect to the SharePoint Online site

Now, we need to connect to the SharePoint site where the above lists are exists. From the app, click on Data icon -> Add data -> Connectors and then select SharePoint. If you have not signed it before it will ask you to sign in with your Office 365 credentials.

Then either you can enter the SharePoint site URL or even you can select the site where your lists exists.

Here choose both the list and click on the Connect button.

Then you should be able to see both the data sources like below:

Step-3: Add the controls and design the form

Now, we will design the form. Basically, we will add two Power Apps dropdown control to populate the Employee Country and State. Also, we will add two label cotrols.

Click on the + icon and then choose Drop down from the Input controls.

Here you change the Drop down name and the bind the Country drop down list.

From the Properties window, choose the Employee Country data source in Items and in the Value choose the Title column.

It will populate all the Countries. If you want to bind only the distinct values then write a formula in the Items formula box.

Distinct('Employee Country',Title)

It looks like below:

It will now bind all the distinct values in the Country drop down list.

Step-4: Add the Cascading lookup logic

Now, let us see how to write the logic to bind the State drop down list based on the selected country from the Country dropdown list.

Select the Employee State drowp down list and then in the Items properties write the following formula.

Filter('Employee State',Country.Value = DDLCountry.Selected.Result)

Make sure to choose the Value properties to Title column like below:

In the same way, if you want to populate only the distinct values then write the below formula in the Items properties.

Distinct(Filter('Employee State',Country.Value=DDLCountry.Selected.Result),Title)

This is how we can bind the state drown down list.

Step-5: Save, Test and Publish the App

We have added the cascading dropdown list logic, now let us save, test and publish the app. But let us first preview the app see the logic. Click on the preview icon to preview the app.

Then you can Save, and share the Power Apps app.

This is how to implement, cascading dropdown in powerapps using sharepoint data.

Now, let us see an example of a choice column.

PowerApps Cascading Dropdown

Powerapps Cascading Dropdown means, One dropdown control value depends on other dropdown control values. That means, When a user selects an option from one dropdown control, then the values filter in another Dropdown control.

For example, Suppose there is a Dropdown control named Country and it has some values like “INDIA”, “AUSTRALIA”, “AMERICA”, “CANADA”, “GERMANY” etc.

Similarly, there is another Dropdown control named City and it has specific city values based upon the Country Dropdown.

When a user will select any specific country from the Country Dropdown, then in the City Dropdown, it will show the specific city of that specific country that the user has chosen.

PowerApps Cascading Dropdown SharePoint List

Here, We will see how the PowerApps Cascading Dropdown control works with the SharePoint Online list.

I have a SharePoint List named Gadget Details. This list has these below columns:

• Title: By default, it is a single line of text column, I just renamed it to Gadget Name.
• Brand: This is also a single line of text column.

And I have inserted some records into this list as you can see in the below screenshot.

To work with the Powerapps Cascading Dropdown from SharePoint List, We need to follow these below things as:

• Sign in the PowerApps app with your credentials.
• Create a blank new Canvas app and choose any one Layout either Tablet or Phone.
• At first, Connect the SharePoint Data source (View -> Data sources -> Search SharePoint in the search bar -> Add a new or existing connection -> Select the specific SharePoint site -> Choose SharePoint List -> Connect) to the Powerapps screen. Then, the SharePoint list will be added as shown below.

Next, Insert these below Powerapps Input controls as:

• Insert two Label controls (Insert -> Label) and rename it to “lblGadgetName” and “lblBrand“.
• Set one Label Text property to Gadget Name and other Label Text property to Brand as shown below.
• Similarly, Add two Dropdown controls (Insert -> Input ->Dropdown) and rename it to “ddGadgetName” and “ddBrand” as like below.

Select the Gadget Name Dropdown control and apply this below formula on its Items property as:

Items = Distinct('Gadget Details',Title)

Where,

• Distinct = This PowerApps function helps to remove the duplicate values from the SharePoint list.
• ‘Gadget Details’ = SharePoint list name
• Title = SharePoint list column name which I renamed to Gadget Name

Just preview (F5) the app. You can see all the distinct choice values in the Gadget Name Dropdown control.

Next, select the Brand Dropdown control and apply this below formula on its Items property as:

Items = Distinct(Filter('Gadget Details',Title=ddGadgetName.Selected.Result),Brand)

Where,

• ‘Gadget Details’ = SharePoint list name
• ddGadgetName = Gadget Name Dropdown control name
• Brand = SharePoint list Column name

Now again preview the app and select one choice from the Gadget Name dropdown control.

When you will select any choice, at the same time, you will see all the related Brands of that specific Gadget are appearing under the Brand dropdown control as shown in the below screenshot.

This is how we can create cascading dropdown in powerapps using sharepoint data.

Sort PowerApps Cascading Dropdown Value

Suppose, you want to sort the values that are present in the Powerapps Cascading Dropdown control. To do so, just follow the below things as:

Select the specific Dropdown control that you want the values to be sort and apply this below formula on its Items property as:

Items = Sort(Distinct('Gadget Details',Title),Result)

Where,

• ‘Gadget Details’ = SharePoint List name
• Title = This is the SharePoint column that is having all the Dropdown values and those values you want to sort.

Now preview (F5) the Powerapps app and expand the Dropdown control. You can see all the dropdown values have been sorted as shown in the below screenshot.

This is how, we can sort PowerApps cascading dropdown list values.

PowerApps Cascading Dropdown Sharepoint Lookup

In this scenario, I will explain to you how to use PowerApps Cascading Dropdown using SharePoint Lookup column.

Basically, For this scenario, I have created three SharePoint List named as:

1. Company Info
2. Department Details
3. Employee Info

The Company Info list is having only one column i.e.

• Title: By default, it is a Single Line of Text column. I just renamed it to Company Name.

Also, this list has some records as like the below screenshot.

Below represents the Department Details list which is having below two different data types of columns as:

• Title: By default, it is a Single Line of Text column. I just renamed it to Department.
• Company Name: This is a Lookup column (Title) from the Company Info list as shown in the below screenshot:

This Deaprtment Details list has also some below records.

Third list is the Employee Info list which has contain these below fields as:

• Title: By default, this is a Single line of text column.
• Company Name: This is a Lookup column (Title) from the Company Info list (As I have already given the screenshot).
• Department: This is a Lookup column (Title) from the Department Details list as shown in the below screenshot:

You will not create or insert any record in this Employee Info list. By using the Powerapps Cascade Dropdown and a Button control, you will insert the item into this list. Refer to the below example for better understanding.

Follow these below steps to insert the item into a list using Powerapps Cascade Dropdown and a Button control:

• Create a new Blank Canvas Powerapps app with either a Tablet or a Phone Layout.
• On the Powerapps screen, Connect the SharePoint Data source and add all the three SharePoint Lists named: “Company Info“, “Department Details“, “Employee Info” as shown below.

Add these below PowerApps controls on the PowerApps screen:

• Add two Labels. Rename one label to “lblDepartment” and other labels to “lblCompanyName”. Set the Text property of one Label is “Company Name” and the other one is “Department”.
• Insert two Dropdown controls. Rename one Dropdown to “ddDepartment” and other one to “ddCompanyName”.
• Add one Button and Rename it to “btnSubmit”. Set its Text property as “Submit”.

Next, select the Company Name Dropdown control and set its Items property as:

Items = 'Company Info'

Where,

• ‘Company Info’ = SharePoint List name

Similarly, select the Department Dropdown control and apply this below formula on its Items property as:

Items = Filter('Department Details', 'Company Name'.Value=ddCompanyName.Selected.Title)

Where,

• ‘Department Details’ = SharePoint List Name which contains the lookup column (Company Name)
• ddCompanyName = Company Name Dropdown control Name

Next, select the Button and set its OnSelect property as:

OnSelect = Patch(
    'Employee Info',
    Defaults('Employee Info'),
    {
        Title: "New Employee",
        CompanyName: {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            Id: ddCompanyName.Selected.ID,
            Value: ddCompanyName.Selected.Title
        },
        Department: {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            Id: ddDepartment.Selected.ID,
            Value: ddDepartment.Selected.Name
        }
    }
);
Notify("Record Saved Successfully!!!");

Where,

• Patch = Patch is the Powerapps function that helps to update the record in the Data source.
• ‘Employee Info’ = This is the SharePoint list which contains the Lookup columns (Company Name and Department) from both the lists (Company Info and Department Details)
• Defaults(‘Employee Info’) = This Default function helps to create a new record in the Employee Info list.
• Title = This is the By default column of SharePoint list (Employee Info) where the “New Employee” will store.
• CompanyName = Lookup column name
• ddCompanyName = Company Name Dropdown control name
• ddDepartment = Department Dropdown control name

If the above code executes perfectly, then a success notification message will appear as “Record Saved Successfully!!!”.

Now preview (F5) the Powerapps app. Select the Company Name from the Dropdown. When you will select any Company Name, all the Departments those are under the Company name will show automatically.

Select any Department and hit on the Submit button. Once you will submit, a success notification message will appear on the top of the page as shown below.

Go to the Employee Info list, there you can see the new record has been added with the selected Company Name and Department as like below screenshot.

PowerApps Cascading dropdown default to blank

Normally what happens is, in the PowerApps, When you are adding a Dropdown control, always it shows the first value by default.

Suppose, you want to set a blank default value to Dropdown control when the user previews it. Follow these below steps to do so:

• At first, Go to your SharePoint list (Gadget Details) and Insert a blank line as like below:

• Next, come back to the Powerapps app. Select the first Dropdown control (Gadget Name) and set its Items property as:

Items = Sort(Distinct('Gadget Details',Title),Result)

• Next, Select the second Dropdown control (Brand) and set its Items property as:

Items = (Sort(Distinct(Filter('Gadget Details',Title=ddGadgetName.Selected.Result),Brand),Result&Blank()))

When you will preview the app, you can see both the Dropdown control will visible with a blank line as by default like the below screenshot:

When the user will select any choice value from the first Dropdown control, then only the selected values (Depends on first Dropdown choice-value) will appear in the second Dropdown control.

This is how, we can set default blank in PowerApps Cascading Dropdown list.

PowerApps Cascading Dropdown excel

Not only you can add only the SharePoint List Data Source, But also, you can use the Excel spreadsheet to work with the PowerApps Cascading Dropdown control.

The only difference between the SharePoint List and Excel spreadsheet is, While you are working with the SharePoint List, that time you are connecting the SharePoint Data Source to the Powerapps app.

But when you will work with the Excel spreadsheet, you need to connect the Excel sheet Data source connector to the Powerapps app.

To connect the Excel spreadsheet in Powerapps, follow this below things as:

• Go to View tab -> Data sources -> Search Import from Excel in the Search bar -> Select your Excel spreadsheet. Now the specific Excel spreadsheet will be added to the Powerapps screen.

Rest all the things are the same, as you have done by taking the SharePoint list data source.

Also, you may like these below Powerapps Tutorials:

• Show hide fields based on dropdown selection PowerApps
• PowerApps camera control + Save captured image in SharePoint
• PowerApps role based security SharePoint example
• PowerApps: Submit data to two SharePoint Lists
• PowerApps Patch Function with examples
• PowerApps set field value based on another field
• Embed PowerApps in SharePoint modern page
• PowerApps toggle control + How to use with example
• How to use date time picker in PowerApps
• PowerApps Search Function + How to use with example
• Cascading dropdown in SharePoint 2013/2016/Online using jQuery
• PowerApps Value Function – How to use

I hope this PowerApps tutorial helps you to learn about the PowerApps cascading dropdown. We also learned, how to create Powerapps Cascading Dropdown using the SharePoint List.

And the below things related to the cascading dropdown list in PowerApps:

• How to sort the Cascading Dropdown Value in PowerApps
• Create PowerApps Cascading Dropdown using the Sharepoint Lookup
• How to set the Cascading dropdown default value to blank in Powerapps
• PowerApps cascading Dropdown excel