Forum Discussion
Combining Columns or replacing
I'd really call that doing it from the Ribbon scott_Abaana - you want several if/then/else constructs. If you can do nested IF() functions in Excel (or IFS()) then you can do this.
From the ribbon, go to Add Columns, then Custom Column, and type in this formula:
if [FREQ] = "Cash" then [Amount] else if [FREQ] = "Yearly" then [Amount] else if [FREQ] = "Monthly" then [Amount] * 12 else if [FREQ] = "Quarterly" then [Amount] * 4 else nullSo intead of creating all of the columns, you just do the math on the [Amount] field.
The if/then/else construct is a bit different than Excel.
- the keywords if, then, and else are always lowercase
- All three are required. Excel lets you leave the last one out and you can do =IF(A1=5,3) and that will return a blank if A1 doesn't equal 5 in Excel. In Power Query, if [Field] = 5 then 3 will return an error because else is missing. I used else null for your final answer, but you can return the 9999999 if you want.
Here is your workbook back. Look at my Single if then else query.
OK so I have uplaoded a small sample file here Sample Power Query
As I went through the problem for me was a little clearer. As you stated I want to use the ribbon as much as possible so I dont mess up the code. As I get more familiar with it I find I can edit and tweak it.
But my challenge was that when you use the ribbon for a conditional column it does not allow you to put in a formula for the output. This is why I ran three columns each calculating the various formulaes needed, and then select which one I want. I guess the solution probably is to run and if else formula, but Not clear if you can do this from the ribbon.
I'd really call that doing it from the Ribbon scott_Abaana - you want several if/then/else constructs. If you can do nested IF() functions in Excel (or IFS()) then you can do this.
From the ribbon, go to Add Columns, then Custom Column, and type in this formula:
if [FREQ] = "Cash" then [Amount] else if [FREQ] = "Yearly" then [Amount] else if [FREQ] = "Monthly" then [Amount] * 12 else if [FREQ] = "Quarterly" then [Amount] * 4 else nullSo intead of creating all of the columns, you just do the math on the [Amount] field.
The if/then/else construct is a bit different than Excel.
- the keywords if, then, and else are always lowercase
- All three are required. Excel lets you leave the last one out and you can do =IF(A1=5,3) and that will return a blank if A1 doesn't equal 5 in Excel. In Power Query, if [Field] = 5 then 3 will return an error because else is missing. I used else null for your final answer, but you can return the 9999999 if you want.
Here is your workbook back. Look at my Single if then else query.
No. It was linked to a file on your hard drive. I pointed it back to the table you had in one of the spreadsheets so I could work on it. If the original source file isn't available. Power Query errors out immediately. Check ou the Source line now - it is to an Excel sheet (or table?) rather than an XLSX file on a hard drive.
Yeah I think it pretty much. I am doing more complex thing in Excel its just getting used to the new language.
My full Power Query has loads of levels to it and takes 2-3 mins to update, but I think its partly becaues I am running two many calculations.
edhans Did this file work for you straight away? Because the Data was loaded from itself?
Thats actually really helpful. I think I will build some of my tables in this way. I have also just learnt about the parameters which can be used as quick edits for the file locations and if I can get these all set up I think It will make it easier to relocate PQ