Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous 

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

@Anonymous 


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

Anonymous
Not applicable

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.

@Anonymous 

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 @Anonymous 

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.