In this Power BI blog, we will discuss how to add a conditional column in Power BI desktop.
The conditional column is a new custom column that will display the output based on the condition satisfied in Power BI.
For example, I have a SharePoint list of “10thGradeSheet2019” which is having some of the students result. We will see how to add the conditional column so that the custom column will display the grade value.
How to Connect SharePoint List Data Source to Power BI Desktop?
First I have created a SharePoint Online List of the name “10thGradeSheet2019“. Using Power BI desktop we will add a conditional custom column where we are going to use the “Marks” column.
Open the Power BI desktop in the local system. Pass the Office 365 credentials if asked. By default, the Home tab is selected. Click on the Get Data->More.
Click on “Online Service” in the “Get Data” page. Select the SharePoint Online List and click on “Connect”.
Pass the SharePoint site URL where the list is present. Click on OK.
Select the List Name as “10thGradeSheet2019” and Click on “Load”.
Read How to use Power bi switch function
Add the Conditional Column in Power BI Desktop
On the top of the Power BI desktop, we will get “Edit Queries”->Edit Queries.
It will navigate to a ” Power Query Editor Page”. The Page will look like the below screenshot.
In the “Power Query Editor Page” select on “Add Column”->”Conditional Colum”.
The Conditional Column option will display a new window of “Add Conditional Column” where we will add the rule.
Inside the “New column name” we need to pass the custom calculated column name. In the Column Name dropdown list, I have selected the column name as “Marks(%)”.
Select the condition from the Operator dropdown list. When we are expanding the Value field “ABC” we will get “Enter a value”, “Select a column”. That means we can enter the value manually or we can select the column.
Here I have entered manually 90. In the Output, I have entered “Outstanding”. From the Add rule box, we can add more rules.
I have added 5 more rules. It is the same as if and else if condition. In the if condition added some of the rules and in else I have added Fail manually. When the above 6 conditions will not satisfy then the else part will execute. Click on OK.
Each condition we can move up and down. We can also Delete the condition in the Add Conditional Column.
Now we will see our custom conditional column have added.
Go to the Report page. We will get the Custom Conditional column under the SharePoint list name. I have added a Table Visualization and in the Values, fields added all the column name which we want to display
You may like following Power BI tutorials:
- How to change data source in Power Bi
- Power BI Admin Portal
- Power BI Quick Insights
- Power BI Group By Examples
- Power bi shared dataset permissions management
- Scheduled Power BI report data refresh
- Power BI nan error (Not a number) while dividing by Zero
- Power BI report using People Picker Field
- Power BI Measure Sum and Subtract Example
- Data Labels in Power BI
- Power bi table visualization
- Subtraction in Power bi using DAX
- How to split column in Power bi
- How to Create Report in Power BI from SharePoint List
This Power BI tutorial, we learned how to a conditional column in Power BI desktop.
Hello Everyone!! I am Bhawana a SharePoint MVP and having about 10+ years of SharePoint experience as well as in .Net technologies. I have worked in all the versions of SharePoint from wss to Office 365. I have good exposure in Customization and Migration using Nintex, Metalogix tools. Now exploring more in SharePoint 2016 🙂 Hope here I can contribute and share my knowledge to the fullest. As I believe “There is no wealth like knowledge and no poverty like ignorance”