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

[nitin_bhatnagar]

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.

[Anonymous]

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]

@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

[nitin_bhatnagar]

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.

[Fowmy · 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 

________________________

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

[mohammedadnant]

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