Forum Discussion
Powerquery- Multiple actions in IF statement
I have created at variable equal to 1. I want to iterate through a column and (if the value is null) do two things:
- Replace the null with my variable
- Update my variable, increasing it by 1
Something like so:
each if [Column] = null then variable AND variable=variable+1 else [Column]
What's the proper syntax to replace AND with?
Hi th3h0bb5 ,
Check if it works:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorViVYyMTUD03mlOTmoDHMLS6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
#"Added Index" = Table.Combine(
{Table.SelectRows(#"Changed Type", each [Column1] <> null),
Table.AddIndexColumn(Table.SelectRows(#"Changed Type", each [Column1] = null), "Index", 1, 1)
}),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Column1", "Column1 - Copy"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"Column1 - Copy", type text}, {"Index", type text}}, "pt-BR"),{"Column1 - Copy", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Column2")
in
#"Merged Columns"
9 Replies
Not sure if you want:
or "if then else if then else" ? I don't know what your logic is from what you posted.
What I'd like is x2 actions to happen during the 'THEN' portion of the IF statement:
IF [column] = null
THEN DoThing1 and DoThing2
ELSE DoThing3
I'm probably going to have to bail on this and call in ImkeF and edhans . Maybe it's just because I can't see the rest of the code or how this is used in context of a column or a function or query but seems to me that you would need to use another let statement.
if blah=blah then let var = blah, var2 = blah in ...
https://bengribaudo.com/blog/2018/01/19/4321/power-query-m-primer-part4-variables-identifiers
Power Query uses lower case and.
Other than that, I don't understand your formula. PQ uses if/then/else, and you can nest if statements.
each if [Column] = null then variable AND variable=variable+1 else [Column]if the column null, you are saying "then variable and variable = variable +1" = that is a boolean logic comparison, which I cannot make sense of. Can you provide an actual example?
'AND' in my example above is a placeholder, as I don't actually now the proper thing to put there. I'm not exactly sure how else to restate this. You can look at the sample input/output columns I provided above if that helps.
In English, I want a line of code that says:- Store a variable equal to 1.
- Go through each row of a column.
- If the current value is null, then replace that null with my variable (which is currently 1). Also add 1 to my stored variable (making it 2).
- Otherwise, leave that current value alone
I want Powerquery to perform 2 actions if my criteria is true. In code-lish it would "IF true THEN do 1 and 2 ELSE do 3."
In most programming languages, this could be easily done with by storing a variable and then iterating through a list with a FOR LOOP.
