Adding new column IF statement in Query table

[KTP_UBS]

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. 

Thanks.

[sdjensen]

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.

[KTP_UBS]

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

Many thanks.

[KTP_UBS]

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 😞

Thanks.

[sdjensen]

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.

[KTP_UBS]

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

[sdjensen]

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?

[KTP_UBS]

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

[sdjensen]

I made a few corrections to the file and it's working now - for some reason the relationship between the budget table and project table was deactivated. Try downloading the new file using the same link.

[sdjensen · Accepted solution]

@KTP_UBS - did you solve your problem?

[KTP_UBS]

Not particularly, I found another way in tweaking ways of how data is processed in the Query table. Anyway, thanks for the sample file. It is helpful for my next dashboard. Many thanks @sdjensen.

[sdjensen]

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]

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