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.
I am not 100% sure I understand what you are asking scott_Abaana but see if this helps. In Power Query, all steps are tracked through the Applied Steps pane.
You can go back up to previous steps and redo/modify them. As long as you are careful, all changes will just work. If you break something, like rename a column in the 3rd step that the 5th step needs, you just go to the 5th step and tell it to reference the new column name in the Power Query formula bar.
If this is not what you are asking, can you provide some screenshots and/or data of the issue with a bit more information?
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
Yes I understand what you have said, but I am not sure I have explained well.
Will Try and explain a little better
To Start lets Say you have 5 Columns of Data A-E
You want to peform some calculations on Columns C and D based on the 3 different values in E. (ie, if E is monthly you would applies different formula, than if it was Yearly)
So I have been creating:
- F - Conditonal calaulatuon based on E Criteria 1
- G - Conditonal calaulatuon based on E Criteria 2
- H - Conditonal calaulatuon based on E Criteria 3
- I - Combiming the resulst of F,G,H based on the values in E
- Finally Hide F,G,H
I know you could put in a if /else, but the nice part of PQ is that you can do it in steps rather than large nested formulas.
So my Question: Is this is the best approach.
Eg I read something that suggested you could replace values in a column. Could you.
create F, Conditonal calaulatuon based on E Criteria 1
- Replace in F, Conditonal calaulatuon based on E Criteria 2 etc
This would also be helpful if you forget a Criteria.
Hello scott_Abaana
your approach is kinda tedious. You can use a Table.ReplaceValue to do everything in one step. I made a quick example for you
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYmcgdgFiIwMjA6VYHWwShpZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}, {"E", Int64.Type}}), ChangeE = Table.ReplaceValue ( #"Changed Type", each [E], (row)=> let F = if row[E]=2020 then "twentytwenty" else "twentynineteen", G = if row[E]=2020 then "laterthen2019" else "laterthen2018", H = if row[E]=2020 then "higher" else "lower", Combine = if row[E]=2020 then Text.Combine({F,G,H}) else Text.Combine({H,F,G}) in Combine, Replacer.ReplaceValue, {"E"} ) in ChangeECopy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyJimmy801 Yeah it is tedious, (hence the Question) 🙂
This type of problem comes up a lot in my current Build.
Will have a look at the code and see how it works for me.
Many thanks.
Ps is there a built option in PQ that does the same/similar thing?
