cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
KTP_UBS Regular Visitor
Regular Visitor

Adding new column IF statement in Query table

Hello,

 

I am relatively new to PowerBI and really appreciate it if you could help with my question below:

 

By referring to the excel screenshot below, I would like to add BudgetIndex2 and BudgetIndex3 in my query table by writing the formula as in column D and column E. The query table is the one when I click "Edit Queries" in Power BI Desktop. 

 

IfstatementPowerBI.JPG

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
sdjensen Senior Member
Senior Member

Re: Adding new column IF statement in Query table

@KTP_UBS - did you solve your problem?

/sdjensen
12 REPLIES 12
sdjensen Senior Member
Senior Member

Re: Adding new column IF statement in Query table

Hi,

 

You can read about if statements in Power Query (Query Editor) in this page: http://www.excelguru.ca/blog/2014/08/27/the-if-function-in-power-query/

 

However I would recommend that you make these measures in your model instead. If you calculate values like this you will get wrong totals when using the value in your visuals.

/sdjensen
sdjensen Senior Member
Senior Member

Re: Adding new column IF statement in Query table

Just noticed you want to compare values in different rows. Don't really have a solution for that.

/sdjensen
KTP_UBS Regular Visitor
Regular Visitor

Re: Adding new column IF statement in Query table

Hi,

 

I have came across the link you suggested before posting the question. The example in the link shows the formula on checking the text range but not comparing values in different rows.

 

Could you please suggest the formula to use in either Query Editor or using Measure?

 

Or could you suggest how I could process the following scenario by referring to the screenshot below?

 

1. A project has one budget but may receive one or more Grant

2. I would like to show chart that compare total grant and total budget of year 2016. By taking the screenshot as an example, the total grant for year 2016 = 75392 and total budget for year 2016 = 553725

 

IfstatementPowerBi2.JPG

 

 

Many thanks.

 

 

 

sdjensen Senior Member
Senior Member

Re: Adding new column IF statement in Query table

I created a small example file... have a look at it and see if that is what you want to do. If it is then you should split the 2 values in different tables. What I did is create a new query with the first query as a source and then select the distinct values of year, project id and total budget into this table and then connected these 2 tables with a year table and project id table.

 

If this is a step in the right direction you would want to hide some columns later from the first table that no longer make sence. Perhaps even better design your queries so you load the grant values in one query and budget values in another - I just made a quickie to show you how you could model this.

 

You can download my sample here: SampleGrantBudget.pbix

/sdjensen
KTP_UBS Regular Visitor
Regular Visitor

Re: Adding new column IF statement in Query table

Hello,

 

Thanks for the sample file.

 

It works in a way if I pull out the Total Grant and Total Budget from two data sources / tables.

 

Could you please advise if I would like to add a slicer of Project ID so that when I select a project I could see the total grant and total budget for that particular project?

 

Please see screenshot for your reference. I don't think I can attach the PB file here in the forum Smiley Sad

 

SamplewithSlicer.JPG

 

Thanks.

 

sdjensen Senior Member
Senior Member

Re: Adding new column IF statement in Query table

Try adding the project id from the project id table instead of the one from the budget table - The one in the budget table should be hidden in the model since it's only used to create a relationship from the budget table to the project id table.

/sdjensen
KTP_UBS Regular Visitor
Regular Visitor

Re: Adding new column IF statement in Query table

Thanks. Could you please advise on how to write a If statement that compare rows values in the Query Editor? 

sdjensen Senior Member
Senior Member

Re: Adding new column IF statement in Query table

I am not sure how to compare data from 2 different rows, but why do you need that? Does my sample not solve your problem?

/sdjensen
KTP_UBS Regular Visitor
Regular Visitor

Re: Adding new column IF statement in Query table

Nope, it does not. I dragged the Project ID from the Project table in the sample file. The changes on the project ID slicer only affects the Total Grant not the Total Budget.

 

Thanks