Forum Discussion
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.
Thanks.
12 Replies
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.
Just noticed you want to compare values in different rows. Don't really have a solution for that.
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.
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
