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"
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.