Forum Discussion
Powerquery- Multiple actions in IF statement
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"
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
Greg_Deckler Exactly what I was looking for! Very helpful for retrieving data only if a certain parameter condition is fulfilled.
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])
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"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.
