Powerquery- Multiple actions in IF statement

[th3h0bb5]

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?

[edhans]

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?

[th3h0bb5]

'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:

1. Store a variable equal to 1.
2. Go through each row of a column.
3. 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).
4. 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. 

[Greg_Deckler]

Not sure if you want:

or "if then else if then else"  ? I don't know what your logic is from what you posted.

[th3h0bb5]

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

[Greg_Deckler]

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

[pkoetzing]

@Greg_Deckler Exactly what I was looking for! Very helpful for retrieving data only if a certain parameter condition is fulfilled.

[th3h0bb5]

I'll check out the article!

My goal is to create an auto-incrementing Index but only for rows where my Key is Null (it's a long, messy story). If Column1 below is what I had, my desired output is Column2.

Column1	Column2
123	123
456	456
	1
	2
789	789

To do this, I am wanting create a Conditional column like so:

Table.AddColumn(
     Source,

     "Column2",

     each

          if [Column1] = null

          then indexVariable and indexVariable=indexVariable+1

          else [Column2])

[camargos88 · Accepted solution]

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"

[th3h0bb5]

@camargos88 Thanks! I ended up doing something very similar to your solution. I created an index column and just used that to replace my null values instead of a an incrementing variable.