Forum Discussion

th3h0bb5's avatar
th3h0bb5
Resolver II
6 years ago
Solved

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 Somet...
Need Help
  • camargos88's avatar
    camargos88
    6 years ago

    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's avatar
camargos88
Community Champion
6 years ago

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's avatar
th3h0bb5
Resolver II
6 years ago

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.