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.
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
ChangeE
Copy 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
Jimmy
Jimmy801 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?
Can you provide data scott_Abaana - I am sure we can get you some code that works. As you are new to Power Query, I prefer to do it via the user interface if possible as that is the easiest to understand and edit vs custom M code that, while it may work and work wonderfully, you must fully understand it to edit it even slightly. If done through the ribbon and menus though it is easier for even more seasoned users to edit and understand.
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 Forumedhans I have created a sample file. I am just rying to figure out how to upload
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.