cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nitin_bhatnagar
Resolver I
Resolver I

How to check inside a model that if a query returns a particular column

I have a column like "Total = [Column 1] + [Column 2] + [Column 3]".

 

Now I want to check whether a [Column 3] extsts or not and if yes then add that to total else not.

For ex., "Total = [Column] + [Column 2] + (IF [Column 3], [Column 3], 0).

 

I am getting error if I use above formula and that [Column 3] does not exists.

I am new to Power BI and hence don't know if certain check like that can be made or not. Please help.

1 ACCEPTED SOLUTION

@nitin_bhatnagar 

If the column names are fixed and known then you do something like below in PQ.

Add Column like 

 = try [Custom.1]+[Custom.2]+[Custom.5] otherwise [Custom.1]+[Custom.2]

 In my example, I do not have Column.5 so it's not considered 

 

Fowmy_0-1602068472010.png

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi,

I have a similar kind of requirement. My query returns a number of fields of which I am using three fields to generate a report. Fields are ID, Description and Quantity. The requirement is such that to view in the form of pivot table (Matrix equivalent in Power BI) with Id in Rows, Description in Columns and Quantity in values. The query generates different values for Description based on time of execution and out of the values 4 of them belong to a category (Reason for cancellation). So for example there can be only two values in description related to cancellation or maybe the query can generate all four values related to cancellation. Now I need to create a new column Total adding all the cancelled reasons together. How can I achieve this when the columns are dynamic?

 

Based on the above suggestions I was trying a nested try otherwise something like this below. I am not sure if this is a corrct way to frame the custom column calculation. It returns all null values. 

try [col 1] + [col2] + [col3] + [col4] otherwise try [col1] + [col2] + [col3] otherwise try all other 3 column combinations otherwise try all 2 column combinations otherwise try all single column.

 

Is there any better way to solve the problem?

Fowmy
Super User
Super User

@nitin_bhatnagar 


What do you mean by "Column Exist?" . Do you need to check if a value exists in Column 3 then?

This is the model not in Power Query

Total = [Column 1] + [Column 2] + (IF( [Column 3]=Blank(),0, [Column 3]))

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Actually, In Power Query, I am getting certain columns which are to be added in Model. But going on to generalize that query on certain other similar projects, I get some additional columns in Power Query. Hence, the Total column in Model can't be evaluated like that. It should check for the existence of certain ones and then add, otherwise it throws error.

 

Though it seems simple but I am not able to generalize it on multiple projects.

@nitin_bhatnagar 

If the column names are fixed and known then you do something like below in PQ.

Add Column like 

 = try [Custom.1]+[Custom.2]+[Custom.5] otherwise [Custom.1]+[Custom.2]

 In my example, I do not have Column.5 so it's not considered 

 

Fowmy_0-1602068472010.png

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

mohammedadnant
Impactful Individual
Impactful Individual

Hi @nitin_bhatnagar 

 

You cann't do this way, column3 will be there forever, you can check for value greater than 0 or not like this...

Total = [Column] + [Column 2] + (IF [Column 3]>0, [Column 3], 0)

 

Thanks & Regards,

Mohammed Adnan

www.youtube.com/taik18

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!

Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.